CODEWITHSUNDEEP
mysqlprimary-key
Kraken
Kraken

Reputation: 24243

Resetting the primary key to 1 after deleting all the data

So i have MySql and i have a table user with a user_id column and it is the primary key and auto incremented. Now when i delete all my data from the table and add the new one, the user_id does not start from 1 but from the number it had before deletion. What if i want to reset it without dropping the whole table and creating it again.

Upvotes: 15

Views: 34866

Answers (3)

fatima Ajij
fatima Ajij

Reputation: 192

you can use DBCC check identity to reset your Primary key. here is the Sytax:

DBCC CHECKIDENT(TableName,RESEED,0)

Upvotes: 0

IcyNets
IcyNets

Reputation: 356

The code below is best if you have some data in the database already but want to reset the user_id to 1 without deleting the data. Copy and run in SQL command

ALTER TABLE members DROP user_id;
ALTER TABLE members AUTO_INCREMENT = 1;
ALTER TABLE members ADD user_id int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

Upvotes: 4

Elgoog
Elgoog

Reputation: 2275

ALTER TABLE some_table AUTO_INCREMENT=1

So some_table would be the table you want to alter.

You could also use:

TRUNCATE TABLE some_table

This will reset the Auto Increment on the table as well as deleting all records from that table.

Upvotes: 29

Related Questions