Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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

I have a MySQL table that looks like this:

enter image description here

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.

like image 727
adit Avatar asked Jun 19 '13 02:06

adit


People also ask

Can we modify primary key in a table?

You can modify the primary key of a table by changing the column order, index name, clustered option, or fill factor.

How can you change the primary key value of a row in MySQL?

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

Can Atable have two primary keys?

Each table can only have one primary key. Access can automatically create a primary key field for you when you create a table, or you can specify the fields that you want to use as the primary key.


3 Answers

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
like image 74
Sergey Kalinichenko Avatar answered Oct 20 '22 02:10

Sergey Kalinichenko


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.

like image 23
Gordon Linoff Avatar answered Oct 20 '22 03:10

Gordon Linoff


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;
like image 38
fpinvidio Avatar answered Oct 20 '22 01:10

fpinvidio