How to Reset AUTO_INCREMENT in MySQL After Deleting Rows

Problem Statement Recently, while working with a MySQL database, I encountered an issue related to AUTO_INCREMENT behavior in a table named News. Initially, the table had five records, but I deleted the last two. Later, when inserting new data, I noticed that the id column was not sequentially picking up from where I expected. Instead of starting from 4, the next inserted record had an id of 6, skipping the deleted values. This happens because MySQL's AUTO_INCREMENT does not automatically adjust after deletions. It simply remembers the highest used id and continues from there. If you’re facing a similar issue and want the next inserted record to follow a custom id sequence, here’s how you can solve it. Solution: Reset AUTO_INCREMENT The fix is quite simple. You need to reset the AUTO_INCREMENT value to the desired number using the ALTER TABLE statement. SQL Query to Reset AUTO_INCREMENT ALTER TABLE News AUTO_INCREMENT = 4; How This Works: The ALTER TABLE command resets the AUTO_INCREMENT counter. In this case, setting AUTO_INCREMENT = 4 ensures that the next inserted record starts from 4 instead of skipping numbers. This works only if no higher id values exist in the table. If you already have an id = 6, setting AUTO_INCREMENT = 4 will not work since 6 is already taken. Check Your Highest ID Before Resetting To avoid conflicts, you can check the highest existing id before resetting: SELECT MAX(id) FROM News; Then, set AUTO_INCREMENT accordingly. Things to Keep in Mind ✅ MySQL & MariaDB Support: This method works for MySQL and MariaDB databases. ✅ Does Not Affect Existing Data: Resetting AUTO_INCREMENT does not modify existing records, only future inserts. ✅ Primary Key Integrity: Ensure there are no conflicting IDs before resetting AUTO_INCREMENT.

Mar 29, 2025 - 01:29

How to Reset AUTO_INCREMENT in MySQL After Deleting Rows

Problem Statement

Recently, while working with a MySQL database, I encountered an issue related to AUTO_INCREMENT behavior in a table named News. Initially, the table had five records, but I deleted the last two. Later, when inserting new data, I noticed that the id column was not sequentially picking up from where I expected. Instead of starting from 4, the next inserted record had an id of 6, skipping the deleted values.

This happens because MySQL's AUTO_INCREMENT does not automatically adjust after deletions. It simply remembers the highest used id and continues from there.

If you’re facing a similar issue and want the next inserted record to follow a custom id sequence, here’s how you can solve it.

Solution: Reset AUTO_INCREMENT

The fix is quite simple. You need to reset the AUTO_INCREMENT value to the desired number using the ALTER TABLE statement.

SQL Query to Reset AUTO_INCREMENT

ALTER TABLE News AUTO_INCREMENT = 4;

How This Works:

The ALTER TABLE command resets the AUTO_INCREMENT counter.
In this case, setting AUTO_INCREMENT = 4 ensures that the next inserted record starts from 4 instead of skipping numbers.
This works only if no higher id values exist in the table. If you already have an id = 6, setting AUTO_INCREMENT = 4 will not work since 6 is already taken.