Page 1 of 1

skipped value with auto increment

Posted: Tue Oct 05, 2004 1:53 pm
by bimo
Hi, all. I'm sorry if this has already been asked but I couldn't find anything on it.

Small problem; not really a problem, actually, just annoying. It goes like this:

My primary key is set to not null auto_increment and each record is assigned a value consecutively greater than the last by one... except, that is, if I have deleted my last record. Then it skips the value that the deleted record used to have... and the primary key value of the deleted record is never used again. Why is this? Is there any way to have it automatically put the newest record in empty slots? Would I have to runa aloop that searches for any empty PK values and inserts the new record there?

Thanks.

Posted: Tue Oct 05, 2004 1:55 pm
by malcolmboston
there is a way to "re-assign" the autoincrement number however it currently eludes me.

Use phpmyadmin and go to "operations" is is listed at the bottom of this page, after this you will then get the query to use in the future.

Hope this helps

Posted: Tue Oct 05, 2004 2:00 pm
by feyd
auto_increment is incremented each time a record is inserted. It does not count backwards, ever. You can soft reset the value using

Code: Select all

ALTER TABLE `table_name` AUTO_INCREMENT = XXX
switch out XXX for the new value you wish, if the PK used that slot, I believe it'll reset to 1 past the max you have.

As for inserting on an "empty" spot, I wouldn't bother.. Just make your PK field large enough to hold a lot of records, or don't use an auto_increment.

Posted: Tue Oct 05, 2004 2:04 pm
by bimo
Thank you!

Posted: Wed Oct 06, 2004 3:17 am
by twigletmac
Note that auto-increment generates a unique key for each record hence why you wouldn't want to be reusing the values If you need to output the records as 1, 2, 3, 4 etc. then you can do that in you PHP code by counting which record you're up to - a primary key should not be used for that job.

Mac