Reorder / Reset Auto Increment Primary Key

Question

I have a MySQL table with an auto increment primary key. I deleted some rows in the middle of the table. Now I have, for example, something like this in the ID column: 12, 13, 14, 19, 20. I deleted the 15, 16, 17 and 18 rows.

I want to reassign / reset / reorder the primary key so that I have continuity, i.e. make the 19 a 15, the 20 a 16, and so on.

How can I do it?

Solution

Even though this question seems to be quite old, will post an answer for someone who reaches in here searching.

SET @count = 0;
UPDATE `users` SET `users`.`id` = @count:= @count + 1;

If the column is used as a foreign key in other tables, make sure you use ON UPDATE CASCADE instead of the default ON UPDATE NO ACTION for the foreign key relationship in those tables.

Further, in order to reset the AUTO_INCREMENT count, you can immediately issue the following statement.

ALTER TABLE `users` AUTO_INCREMENT = 1;

For MySQLs it will reset the value to MAX(id) + 1

Comments

Popular posts from this blog

About 12 Alwars

Post thumbnails from external URLs don’t appear in Blogger. How to resolve?

What is the unicode character for the close symbol?