Resetting A Primary Key

[kevinguill]

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

[NightShift58]

MySQL:

ALTER TABLE `myTable` AUTO_INCREMENT=1

[russell]

and for MSSQL

DBCC CHECKIDENT ('table name', RESEED, newSeedValue)

and for either mysql or mssql

Truncate Table tableName

[NightShift58]

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...

[chazzy]

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.

[NightShift58]

Except that he already said the table's empty

What he said/wrote was:

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...

[kevinguill]

yes the table was completely empty. i decided to use this and is exactly what i needed thank again NightShift58

ALTER TABLE `myTable` AUTO_INCREMENT=1

[NightShift58]

You're welcome!