mysql - 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.

Answer 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