How to renumber primary index

Solution 1:

I agree other methods will work but I'm just giving a different idea. This will do without any temp table creation requirements::

SET @i=0;
UPDATE table_name SET column_name=(@i:=@i+1);

Solution 2:

give a try to renumber method of dalmp (DALMP Database Abstraction Layer for MySQL using PHP.)

$db->renumber('table','uid');

basically it does this:

SET @var_name = 0;
UPDATE Tablename SET ID = (@var_name := @var_name +1);
ALTER TABLE tablename AUTO_INCREMENT = 1

Solution 3:

The easiest Solution is:

• Rename the table to tablename_temp
• Create a new table with the old name and the same structure
• INSERT INTO tablename (id, field1, field2, field3) SELECT NULL, field1, field2, field3, ... FROM tablename_temp;
• DROP tablename_temp

This will destroy all of your data if you are using foreign keys and i strongly suggest you leave the ids as they are. A Database is not untidy because the primary keys are not in sequence, a Database is untidy if it's referential integrity (id values pointing to the wrong or non-existant row in other tables) is broken.

Solution 4:

1. Delete the "id" column.
2. Create column named id with auto increment on(and do not allow nulls), it will have the new values you want.
3. Assign this new "id" column as PK.

edited later thanks to comments.