1

I have an invoices table which stores a single record for each invoice, with the id column (int AUTO_INCREMENT) being the primary key, but also the invoice reference number.

Now, unfortunately I've had to manual migrate some invoices generated on an old system which have a five digit id, instead of a four digit one which the current system uses.

However, even when I reset the AUTO_INCREMENT through PhpMyAdmin (Table Operations) back to the next four digit id, it still inserts a five digit one being the higher id currently in the table plus one.

From searching around, it would seem that I actually need to change the insert_id as well as the AUTO_INCREMENT ? I've tried to execute ALTER TABLE invoices SET insert_id=8125 as well as ALTER TABLE invoices insert_id=8125 but neither of these commands seem to be valid.

Can anyone explain the correct way that I can reset the AUTO_INCREMENT so that it will insert records with id's 8125 onwards, and then when it gets to 10962 it will skip over the four records I've manually added and continue sequential id's from 10966 onwards. If it won't skip over 10962 - 10966 then this doesn't really matter, as the company doesn't generate that many invoices each year so this will occur in a subsequent year hence not causing a problem hopefully.

I would really appreciate any help with this sticky situation I've found myself in! Many Thanks

Dave White
  • 321
  • 1
  • 3
  • 13

1 Answers1

1

First thing I'll suggest is to ditch PHPMyAdmin because it's one of the worst "applications" ever made to be used to work with MySQL. Get a proper GUI. My favourite is SQLYog.

Now on to the problem. Never, ever tamper with the primary key, don't try to "reset" it as you said or to update columns that have an integer generated by the database. As for why, the topic is broad and can be discussed in another question, just never, ever touch the primary key once you've set it up.

Second thing is that someone was deleting records of invoices hence the autoincrement is now at 10k+ rather than at 8k+. It's not a bad thing, but if you need sequential values for your invoices (such as there can't be a gap between invoices 1 and 5) then use an extra field called sequence_id or invoice_ref and use triggers to calculate that number. Don't rely on auto_increment feature that it'll reuse numbers that have been lost trough DELETE operation.

Alternatively, what you can do is export the database you've been using, find the CREATE TABLE definition for the invoices table, and find the line where it says "AUTO_INCREMENT = [some number]" and delete that statement. Import into your new database and the auto_increment will continue from the latest invoice. You could do the same by using ALTER TABLE however it's safer to re-import.

N.B.
  • 13,688
  • 3
  • 45
  • 55
  • Thank you for that valuable insight. If there is a 'bodge-job' way of correctly this without recoding my system and adding an extra invoiceRef field this would be preferable. The last process you outlined wouldn't help unfortunately as the latest invoice (10k+) isn't the id I wish the numbering to continue from (8k+) is. Also, changing the `AUTO_INCREMENT` has no effect as it seems that MySQL is ignoring it and working out the next `insert_id` based on the latest invoice id (which is not what I need in this case!) – Dave White Aug 17 '11 at 12:36
  • That's why auto_increment is generally bad choice for invoice reference numbers if the numbers must be re-used. It's a surrogate primary key, it makes sense to the database but not the business. If the only problem is that the client doesn't like to see the gaps between invoices, then it's a nuisance more than a problem. Trying to fix it might create real problems! I suggest doing nothing about it and try to explain the reasons behind it to your client. If that doesn't work out, creating another field and re-numbering the invoices should do the trick. – N.B. Aug 17 '11 at 12:41
  • Okay I've managed to sweet talk my client and he's happy to continue with 10k+ invoice numbers luckily. In future though I will only use `id int AUTO_INCREMENT` for database management and foreign keys etc, not a field that is displayed to the user! Thanks for your help. – Dave White Aug 17 '11 at 13:01