Menu
I created a primary key to be autoincrement.
ID=1, ID=2 ID=3 How can I reset or restart the autoincrement to 1?
633
In MySQL, the syntax to reset the AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = value; table_name. The name of the table whose AUTO_INCREMENT column you wish to reset.
In MySQL, the syntax to change the starting value for an AUTO_INCREMENT column using the ALTER TABLE statement is: ALTER TABLE table_name AUTO_INCREMENT = start_value; table_name. The name of the table whose AUTO_INCREMENT value you wish to change.
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
The starting value for AUTO_INCREMENT is 1, which is the default. It will get increment by 1 for each new record. To get the next auto increment id in MySQL, we can use the function last_insert_id() from MySQL or auto_increment with SELECT. Creating a table, with “id” as auto-increment.
If you use the DBCC CHECKIDENT command:
DBCC CHECKIDENT ("YourTableNameHere", RESEED, 1); But use with CAUTION! - this will just reset the IDENTITY to 1 - so your next inserts will get values 1, then 2, and then 3 --> and you'll have a clash with your pre-existing value of 3 here!
IDENTITY just dishes out numbers in consecutive order - it does NOT in any way make sure there are no conflicts! If you already have values - do not reseed back to a lower value!
152
I'm using SQL Server 2012 and the DBCC CHECKIDENT ("YourTableNameHere", RESEED, 1) causes a value of 2 on the very next insert.
So for SQL Server 2012, change the 1 to 0 to get a value of 1 for your next record insert:
DBCC CHECKIDENT ("YourTableNameHere", RESEED, 0); -- value will be 1 for the next record insert 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
