A community in which webmasters can ask for help with topics such as PHP coding , MySQL , IT jobs, web design, IT security.
Current location:homephp forumphp talk in 2009 yearMySQL: Reorder/Reset auto increment primary key? - page 1
User InfoPosts
MySQL: Reorder/Reset auto increment primary key?#1
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.

posted date: 2009-04-11 08:11:00


Re: MySQL: Reorder/Reset auto increment primary key?#2
I had made out the solution of this problem. click to view my topic...

hope that hepls.

posted date: 2009-04-11 08:11:01


Re: MySQL: Reorder/Reset auto increment primary key?#3
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.

posted date: 2009-04-11 08:20:00


Re: MySQL: Reorder/Reset auto increment primary key?#4
I have other tables that hold a foreign key to this table, but I'm just starting the project so it's OK for me.. Thanks!

posted date: 2009-04-11 08:24:00


Re: MySQL: Reorder/Reset auto increment primary key?#5
It might be best long term if you try and start accepting that your IDs won't always be sequential, otherwise when you start working on bigger projects it'll really drive out crazy!

posted date: 2009-04-11 12:07:00


Re: MySQL: Reorder/Reset auto increment primary key?#6
You may simply use this queryalter table abc auto_increment = 1;

posted date: 2009-10-11 21:31:00


Re: MySQL: Reorder/Reset auto increment primary key?#7
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;

posted date: 2010-06-25 15:19:00


Re: MySQL: Reorder/Reset auto increment primary key?#8
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.

posted date: 2011-03-25 12:21:00


Re: MySQL: Reorder/Reset auto increment primary key?#9
Or, from PhpMyAdmin, remove "AutoIncrement" flag, save, set it again and save.this resets it.

posted date: 2013-05-04 06:03:00


Re: MySQL: Reorder/Reset auto increment primary key?#10
this is great for MyISAM tables. thx!

posted date: 2013-07-03 07:35:00


Re: MySQL: Reorder/Reset auto increment primary key?#11
SET @num := 0;UPDATE your_table SET id = @num := (@num+1);ALTER TABLE your_table AUTO_INCREMENT =1;I think this will do it

posted date: 2013-09-12 02:13:00


Re: MySQL: Reorder/Reset auto increment primary key?#12
This won't work in this case. For ISAM tables, it will set the autoinc value to max(id) + 1. For InnoDB it will do nothing. See alter table docs for changing AUTOINCREMENT dev.mysql.com/doc/refman/5.0/en/alter-table.html

posted date: 2013-12-09 11:10:00


select page: « 1 2 »
Copyright ©2008-2017 www.momige.com, all rights reserved.