auto_increment change

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
sarris
Forum Contributor
Posts: 137
Joined: Mon Dec 04, 2006 2:44 pm

auto_increment change

Post 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?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
Begby
Forum Regular
Posts: 575
Joined: Wed Dec 13, 2006 10:28 am

Post 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.
impulse()
Forum Regular
Posts: 748
Joined: Wed Aug 09, 2006 8:36 am
Location: Staffordshire, UK
Contact:

Post 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,
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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.
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

'nuff said. You'll wind up with a clusterf**** if you mess with it - leave it be as Feyd said.

fv
sarris
Forum Contributor
Posts: 137
Joined: Mon Dec 04, 2006 2:44 pm

Post by sarris »

ooook...got the point.dont play with fire.
thanks
Post Reply