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?

Answer 1

SET  @num := 0;

UPDATE your_table SET id = @num := (@num+1);

ALTER TABLE your_table AUTO_INCREMENT =1;

I think this will do it

Answer 2

You can also simply avoid using numeric IDs as Primary Key. You could use Country codes as primary id if the table holds countries information, or you could use permalinks, if it hold articles for example.

You could also simply use a random, or an MD5 value. All this options have it's own benefits, specially on IT sec. numeric IDs are easy to enumerate.

Answer 3

The best choice is to alter the column and remove the auto_increment attribute. Then issue another alter statement and put auto_increment back onto the column. This will reset the count to the max+1 of the current rows and thus preserve foreign key references back to this table, from other tables in your database, or any other key usage for that column.

Answer 4

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;

Answer 5

You could drop the primary key column and re-create it. All the ids should then be reassigned in order.

However this is probably a bad idea in most situations. If you have other tables that have foreign keys to this table then it will definitely not work.

Answer 6

in phpmyadmin

note: this will work if you delete last rows not middle rows.

goto your table-> click on operations menu-> goto table options->change AUTO_INCREMENT to that no from where you want to start.

your table autoincrement start from that no.

try it.