Resetting the primary key to 1 after deleting all the data

user_216190 0
Viewed 26378

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.

primary-key mysql
3 Answers

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;
user_52604640

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

DBCC CHECKIDENT(TableName,RESEED,0)

user_107925060
Related