Change primary key (id) of a row in a table and shift the others downwards

Question

I have a MySQL table that looks like this:

I wanted to change such that the entry with id 15 (women, dress) now has a primary key of 8 and then the others are shifted by one, so for example Kids Tops now will be 9, etc. Is there an easy way to do this via phpmyadmin or a SQL query? Also because id 15 is already being used as a foreign key somewhere else, I wanted this change to be reflected all over the place in other tables.

Solution 1

One should not change the primary key - ever. Moreover, it may be beneficial to think about PKs as non-numeric values. Imagine that you use autogenerated GUIDs for your primary keys.

If you want to renumber your items, then the column that you want to change should be a separate numeric column, which you treat explicitly as a sequence number of sorts. Then you can do what you want with three UPDATE statements:

update mytable set sequence = -sequence where sequence = 15 -- hide the original 15
update mytable set sequence = sequence+1 where sequence >= 8
update mytable set sequence = 8 where sequence = -15

Solution 2

You cannot change a "primary key", and for good reason. The primary key is likely to be used by other tables to reference a particular row. So, a change would not be local in the table, it would have to be in every referencing table.

If you want to change the values in the column, then you have to first drop the primary key constraint.

If you really have to do such a thing, here are some considerations:

1. To change the value of the primary key, you have to drop the primary key constraint first.
2. Drop all foreign key references to the table. Otherwise, you will either get unexpected errors or unexpected deletes when you change values (on delete cascade).
3. Create a mapping table that has the old value and the new value.
4. Update the values in the main table.
5. Update the "foreign key" references.
6. Re-apply the foreign key constraints on the remote tables
7. Re-apply the primary key constraint on the original table

(I must admit that I might have missed something, because this is not something that I would ever do.)

Changing a primary key, especially one used in foreign key relationships, should not be taken lightly. The purpose of such keys is to maintain relational integrity. You should not be bothered by gaps in the key or lack of sequentiality. If you want a sequential number, you can add that into another column.

Solution 3

Perhaps you should alter your child table FK with an ON UPDATE CASCADE operation so that changes on the parent table get reflected on the child table.

See the MySQL reference for FK's: http://dev.mysql.com/doc/refman/5.1/en/create-table-foreign-keys.html

Later you can execute the following:

UPDATE table SET id = -15 WHERE id = 15;
UPDATE table SET id = id + 1 WHERE id >=8;
UPDATE table SET id = 8 WHERE id = -15;

Comments

[Gordon Linoff]

. . Don't change primary keys. The fact that it is being used emphasizes why you shouldn't change it.

[E Ciotti]

SET foreign_key_checks = 0; <run all the update careful queries>; SET foreign_key_checks = 1;