[SOLVED] skipped value with auto increment

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
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

skipped value with auto increment

Post 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.
malcolmboston
DevNet Resident
Posts: 1826
Joined: Tue Nov 18, 2003 1:09 pm
Location: Middlesbrough, UK

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

Post 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.
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post by bimo »

Thank you!
User avatar
twigletmac
Her Royal Site Adminness
Posts: 5371
Joined: Tue Apr 23, 2002 2:21 am
Location: Essex, UK

Post 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
Post Reply