#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    27
    Rep Power
    0

    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!
  2. #2
  3. Transforming Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Mar 2007
    Location
    Washington, USA
    Posts
    14,295
    Rep Power
    9400
    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.
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    27
    Rep Power
    0
    Originally Posted by requinix
    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.
    It would just make my script alot easier to write if there were no holes in the primary keys.
  6. #4
  7. Hockey face
    Devshed Supreme Being (6500+ posts)

    Join Date
    Nov 2001
    Location
    St. Catharines, Canada
    Posts
    8,147
    Rep Power
    1317
    no it wouldn't really. your script should get the next number from the database, not rely on the numbers being consecutive.
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2006
    Posts
    27
    Rep Power
    0
    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

IMN logo majestic logo threadwatch logo seochat tools logo