How to reset auto-increment value of a column in MySql.
In MySQL, we can define a column as primary key with an auto-increment index. Every time a new row is inserted it increments the value by 1. By default, an auto-increment column is a primary key of a table.
But there might be a situation in which we want to reset an auto-increment value. Suppose you have inserted some rows and later removed it from a table and you want to reset an auto-increment value from 1 or some other value.
To illustrate this concept, let’s create one dummy table.
|
1 2 3 4 5 6 |
CREATE TABLE emp_data ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(200) DEFAULT NULL, age int(3), PRIMARY KEY (id) ); |
Insert some dummy values.
|
1 2 3 4 |
INSERT INTO emp_data(name,age) VALUES('pawan',24), ('ankit',21), ('vikash',45); |
Now we have created a table and inserted three rows successfully.
Let’s delete all rows of a table.
|
1 |
delete FROM emp_data |
If you run delete query all records of a table will be removed. Let’s insert one more record and notice auto-increment value will be 4. So how to reset an auto-increment value.
How to Reset Auto-Increment Value of a Column in MySql
METHOD 1– Reset auto-increment value of a column through Alter command.
Using Alter Command we can reset an auto-increment value of a column.
|
1 |
ALTER TABLE emp_data AUTO_INCREMENT = 1; |
The above query will reset an auto-increment value to 1. You can specify any other value as well.
METHOD 2 : Using Truncate.
The TRUNCATE TABLE statement removes all the data of a table and resets an auto-increment value to zero.
|
1 |
truncate table emp_data |
NOTE : Truncate command will remove all the data from your table.
METHOD 3 : Drop & Recreate existing table
Use this approach if your table is empty (does not have any data). Remove existing table using Drop command and recreate the table.
How to Reset an Auto-Increment Value of a Column in MySql through phpMyAdmin
You can use phpMyAdmin to reset an auto-increment value of a column in MySql.
i) In phpMyAdmin, click on the table you want to reset the AUTO_INCREMENT value.
ii) After that click on the Operations Tab.
iii) In the table options box find the auto_increment field.
iv) Enter the new auto_increment value.
v) Click on the go button for the table options box.
Reset an auto-increment value of a column in MySql
