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.
[SOLVED] skipped value with auto increment
Moderator: General Moderators
-
malcolmboston
- DevNet Resident
- Posts: 1826
- Joined: Tue Nov 18, 2003 1:09 pm
- Location: Middlesbrough, UK
- feyd
- Neighborhood Spidermoddy
- Posts: 31559
- Joined: Mon Mar 29, 2004 3:24 pm
- Location: Bothell, Washington, USA
auto_increment is incremented each time a record is inserted. It does not count backwards, ever. You can soft reset the value usingswitch 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.
Code: Select all
ALTER TABLE `table_name` AUTO_INCREMENT = XXXAs 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.
- twigletmac
- Her Royal Site Adminness
- Posts: 5371
- Joined: Tue Apr 23, 2002 2:21 am
- Location: Essex, UK