MySQL: Reorder/Reset auto increment primary key?

Question

Hi, I have a MySQL table with an auto increment primary keys... 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, 18 and 19 rows.... I want to reassing/reset/reorder the primary keys so I have continuity... make the 19 a 15, the 20 a 16 and sow on....

How can I do it??? Thanks.

Answer 1

You could drop the primary key column and re-create it. All the ids will then be reassigned, I assume in the order in which the rows were inserted.

However, I'm not sure why you'd want to do this, especially if you have other tables that hold a foreign key to this table. If so you would need to update those fields as well, in which case dropping and recreating the column wouldn't be a good solution. You could instead re-name the column, remove the autoinc/primary key property, then create a new autoinc primary key column. You then would have both the old and new ids which you could use to update such foreign keys.

Answer 2

You may simply use this query alter table abc auto_increment = 1;

Answer 3

To reset the IDs of my User table, I use the following SQL query. It's been said above that this will ruin any relationships you may have with any other tables.

ALTER TABLE `users` DROP `id`;
ALTER TABLE `users` AUTO_INCREMENT = 1;
ALTER TABLE `users` ADD `id` int UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;