Menu
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.
598
alter table yourTableName AUTO_INCREMENT=1; truncate table yourTableName; After doing the above two steps, you will get the primary key beginning from 1.
So add one to that number and run the following command: ALTER TABLE `table` AUTO_INCREMENT = number; Replacing 'number' with the result of the previous command plus one and replacing table with the table name. If you deleted all the rows in the table, then you could run the alter table command and reset it to 0.
You can delete (drop) a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. When the primary key is deleted, the corresponding index is deleted. This may be the clustered index of the table, causing the table to become a heap.
If you use TRUNC instead of manually deleting records, your primary key will be reset.
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.
144
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;
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
