SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard masm50's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    2,508
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    MySQL auto_increment and phpMyAdmin

    If I have set the ID column of a table created in MySQL to auto_increment primary key, then how can I get MySQL to start it's counting at the highest number in the DB, and auto_increment from there on, rather than, as it does at the moment, start it's counting at the last value inserted, regardless of whether that value had later been deleted.

    How do I change this?

    Regards

    Tim

  2. #2
    SitePoint Guru Majglow's Avatar
    Join Date
    Aug 1999
    Location
    B-Town
    Posts
    645
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Hello,

    There is no way to do that as far as I can tell just with mysql (there might be a way to do it, but I can't think of one right off the top of my head).

    In the mysql documentation, it says that that is the way that auto_increment works, the only way for it to restart from scratch is deleting everything. I don't think you can do:

    ID
    --
    1
    2
    3
    4

    Delete row with ID = 4, then add a new row where the ID will be 4, if you add a new row, the ID will be 5

    ID
    --
    1
    2
    3
    5

    Carl
    Ohai!

  3. #3
    Dumb PHP codin' cat
    Join Date
    Aug 2000
    Location
    San Diego, CA
    Posts
    5,460
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thats correct, the reason is the auto_increment number is only a number to refer to the record only, meaning if you have relations in other tables with those ids, you don't want them overriding existing relations once a record is deleted. Follow me?
    Please don't PM me with questions.
    Use the forums, that is what they are here for.

  4. #4
    SitePoint Wizard masm50's Avatar
    Join Date
    Oct 2001
    Location
    UK
    Posts
    2,508
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)
    Freddydoesphp,

    I understand your reason completely, it is just from creating a database from scratch it is a bit annoying that I can't just 'undo' a review.

    But hey, it isn't very important.

    Cheers for the help,

    Tim


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •