Reorder Auto-Increment Primary Key in MySQL

In MySQL, when rows are deleted from a table with an AUTO_INCREMENT primary key, gaps in the sequence may appear. While this typically doesn't impact functionality, there may be cases in the early development stage where maintaining a continuous primary key sequence is preferable. This tutorial shows how to reorder auto-increment primary key in MySQL.

If the table does not have foreign key constraints, you can safely reorder the primary key.

Reorder ID values sequentially by running the following SQL query:

SET @count = 0;
UPDATE table_name SET id = @count:= @count + 1;

Once IDs are reordered, reset the counter to start from the highest current value:

ALTER TABLE table_name AUTO_INCREMENT = 1;

Important notes:

• Foreign keys: if other tables reference the id column, reordering may break those relationships.
• Data integrity: if primary keys are used in external applications, reports, or logs, changing them might cause inconsistencies.
• Performance considerations: reordering a large table may take time.