Hi! If you are newbie to MySQL, then you will definitely benefit from this tip I’m going to share today. Auto Increment Counter is one of the prominent feature of MySQL Database and it’s a general practiced way to set the primary key columns to auto increment indexing. By this way whenever a new row is inserted, the counter increments automatically by ‘1’. But the method has one limitation to it for the auto-increment counter will not reset the value by itself even if you delete all the entries in the table.
Imagine you have a mysql table with a bunch of data say 10 rows already but you want to delete and start with fresh set of entries. In such case even if you delete all those rows, the new record would start from ‘11’ and not ‘1’ for auto increment column. But this doesn’t mean you can’t reset it by yourself.
Reset Auto Increment Counter in MySQL
Dropping a table and recreating it will reset auto-increment counter to start from ‘1’ but this is not very desirable solution and I’ll tell you the proper way to reset it.
Method 1: Alter Auto Increment Column of the Table
The best solution to reset autoincrement counter is by modifying the value using ALTER command.
ALTER TABLE tblname AUTO_INCREMENT = 1;
One thing to note here is, if there are some records already present in the table, then you cannot reset it to a value less than or equal to any values that are already there.
Suppose the maximum value present in the auto-increment field is ‘10’ then you cannot reset it to anything less than that. But setting it to some greater value like ‘15’ or ‘20’ is acceptable.
Method 2: Truncating the Table
Another method to reset auto increment counter is by using TRUNCATE command. This will delete all the rows from a table and automatically resets the counter to 1.
TRUNCATE TABLE tblname;
Make sure there are no foreign key records present as truncate command tend to delete all records and reset the counter irrelevant to foreign-key constraint.
Read Also:I hope this mysql tutorial is helpful and you can use the above two methods to easily reset the auto increment value in mysql. The ALTER method is very safe to use as there would be no data-loss but use the latter one with caution.
