October 6th, 2012, 08:02 PM
MySQL PRIMARY KEY Question
Hi, I have a question about the behavior of the PRIMARY KEY in MySQL.
Lets say I have a database with 10 entries the primary keys are 1-10 respectively.
Lets say I then delete the entry with a primary key of 7.
The next time I enter an entry it gets assigned a primary key of 11. Is there anyway to make the database realize that the primary key 7 is the next primary key that should be assigned? Or would I have to make some kind of script that runs through the table to find gaps in the primary key.
It would be really nice if the database could somehow set the next primary key to 7 (or whatever entry was deleted) and not 11.
Thanks in advance!
October 6th, 2012, 08:56 PM
Don't do it. AUTO_INCREMENT primary keys are not meant to be entirely consecutive and sequential. Let them alone, holes and all.
If you need something sequential then that's a different problem.
Last edited by requinix; October 6th, 2012 at 08:59 PM.
October 6th, 2012, 09:02 PM
It would just make my script alot easier to write if there were no holes in the primary keys.
Originally Posted by requinix
October 6th, 2012, 10:18 PM
no it wouldn't really. your script should get the next number from the database, not rely on the numbers being consecutive.
October 7th, 2012, 11:27 AM
Ok, I figured it wasnt possible, thanks for the replies guys, and your correct there is other ways i could make my script.
Thanks again, much appreciated