Page 1 of 1

auto_increment change

Posted: Wed Dec 13, 2006 11:27 am
by sarris
How can i change the auto_increment value manualy for a table?
When i delete rows from a table the auto_increment doesnt change and that a bit annoying. Can is there a way to change it manually?
Is there any trick to use in order to set auto_increment value to fill in the gaps? For example i have the value id, and have 5 records with id 1,2,3,4,5. I delete record 4 and i want next time, the added record to get id 4, while the next from that gets id 6...Is that possible?

Posted: Wed Dec 13, 2006 11:31 am
by feyd
It's not supposed to change and you aren't supposed to change it. Numbers it generates are not supposed to repeat, ever. If you want to fill in gaps and so forth you shouldn't be using an automatically incremented field.

Posted: Wed Dec 13, 2006 11:32 am
by Begby
One rule about a primary key is that it never ever gets reused or changed. This behaviour is by design when using auto increment as a primary key. I don't know how to make it function otherwise.

What exactly are you trying to do anyways? There should be another way to accomplish the functionality you want.

Posted: Wed Dec 13, 2006 12:43 pm
by impulse()
It's possible to do through phpMyAdmin. Open the table and click 'Operation' and you should see a text box named 'Auto_increment' which contains its current value. I'm not sure about changing it through a MySQL prompt, sorry.

Regards,

Posted: Wed Dec 13, 2006 10:09 pm
by fractalvibes
feyd wrote:It's not supposed to change and you aren't supposed to change it. Numbers it generates are not supposed to repeat, ever. If you want to fill in gaps and so forth you shouldn't be using an automatically incremented field.
Exactly - you don't want to muck around with it - leave it be and let it work as designed. Even if phpMyAdmin will let you, don't - you are just asking for trouble and you will no doubt break something and have to have your tables restored from backups...

Think of a more creative solution to what you are trying to accomplish.

fv

Posted: Wed Dec 13, 2006 10:15 pm
by feyd
It won't break anything doing it unless you use table relationships. It just wasn't designed so you can be willy-nilly with it. Either use it as intended or don't use it at all.

Posted: Wed Dec 13, 2006 10:33 pm
by fractalvibes
Maybe it won't actually "break" the DB, but could easily break relationships with other tables, and by virtue of that, applications that use that/those tables! Plus I don't know what the effect on existing rows would be if the start value, increment or decrement,and increment/decrement amount were changed, nor what would happen with a reload of data from a backup afer such was done.

fv

Posted: Wed Dec 13, 2006 10:37 pm
by feyd
It only affects new records after the fact.

It would require moving records to new IDs to mess relationships (provided they aren't foreign key.)

Either way, it's not recommended to fiddle with the setting unless you know exactly what you are doing.

Posted: Wed Dec 13, 2006 10:45 pm
by fractalvibes
'nuff said. You'll wind up with a clusterf**** if you mess with it - leave it be as Feyd said.

fv

Posted: Thu Dec 14, 2006 9:36 am
by sarris
ooook...got the point.dont play with fire.
thanks