www.webdeveloper.com
Results 1 to 11 of 11

Thread: Resetting A Primary Key

  1. #1
    Join Date
    Oct 2006
    Posts
    53

    Resetting A Primary Key

    Ok so have deleted previous entries in the database but know when something new is entered into the database the primary key logs it has entry number 46. i want it to log as number 1. can i reset the primary keys to so new entries start at 1

  2. #2
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    MySQL:
    Code:
    ALTER TABLE `myTable` AUTO_INCREMENT=1

  3. #3
    Join Date
    Feb 2003
    Posts
    2,745
    and for MSSQL
    Code:
    DBCC CHECKIDENT ('table name', RESEED, newSeedValue)
    and for either mysql or mssql
    Code:
    Truncate Table tableName

  4. #4
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Quote Originally Posted by russell
    Code:
    Truncate Table tableName
    Truncate would be ideal on an empty table but it could be a radical move for anything else, tantamount to decapitation to cure a headache...

    If the table is not empty, the primary intention for using TRUNCATE should be emptying the table and not just resetting the autoincrement counter.

    If the table is empty, the "ALTER TABLE...AUTO_INCREMENT=1" (or its MSSQL equivalent) is equally efficient, except that if data is present, it will simply the reset the autoincrement counter to the highest ID + 1.

    My concern with "TRUNCATE" is that it has the potential to be very destructive... It should be avoided for such "mundane" tasks and used with care...

  5. #5
    Join Date
    Aug 2005
    Location
    The Garden State
    Posts
    5,634
    Quote Originally Posted by NightShift58
    Truncate would be ideal on an empty table but it could be a radical move for anything else, tantamount to decapitation to cure a headache...

    If the table is not empty, the primary intention for using TRUNCATE should be emptying the table and not just resetting the autoincrement counter.

    If the table is empty, the "ALTER TABLE...AUTO_INCREMENT=1" (or its MSSQL equivalent) is equally efficient, except that if data is present, it will simply the reset the autoincrement counter to the highest ID + 1.

    My concern with "TRUNCATE" is that it has the potential to be very destructive... It should be avoided for such "mundane" tasks and used with care...
    Except that he already said the table's empty. Although in mysql, truncating the table doesn't always reset the key, based on which engine is in use.

  6. #6
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Quote Originally Posted by chazzy
    Except that he already said the table's empty
    What he said/wrote was:
    Quote Originally Posted by kevinguill
    Ok so have deleted previous entries in the database
    ... which could mean that the table is empty or partially empty, depending on what "previous entries" means. Had he said "all previous entries" then I would agree that the table would be empty.

    What he didn't explicitly say was that the table was empty.

    But that is really besides the point. I think that using TRUNCATE is potentially dangerous, especially in a script. I prefer the "ALTER TABLE" variety but, granted, it's a personal choice. There's more than one way to skin a cat...

  7. #7
    Join Date
    Feb 2003
    Posts
    2,745
    agreed. truncate should be used verrrrry carefully. it is also important to know that it is not logged (in sql server) so there is no undo, period.

    in this case i was assuming empty table from OP

  8. #8
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    Quote Originally Posted by russell
    in this case i was assuming empty table from OP
    In all honesty, so did I because he wanted to reset the autoincrement to 1, which would only make sense within the context of an empty table but... in these forums, assumptions are often "punished" harshly by the readership, so - shame on me - I took the easy road. Or so I thought...

    My intention, though, was not to start a fiery discussion on whether the table is empty or not - which is probably irrelevant to the OP - but simply to suggest that, given two options, choose the one that is potentially less destructive.

  9. #9
    Join Date
    Feb 2003
    Posts
    2,745
    LOL didn't think u were trying to punish me. all good brother... and u raised an important point.

    cheers
    russell

  10. #10
    Join Date
    Oct 2006
    Posts
    53
    yes the table was completely empty. i decided to use this and is exactly what i needed thank again NightShift58

    Code:
    ALTER TABLE `myTable` AUTO_INCREMENT=1

  11. #11
    Join Date
    Dec 2006
    Location
    Escaz˙ (Costa Rica) and Mallorca (Spain)
    Posts
    3,234
    You're welcome!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

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



Recent Articles