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.