Insert new row into mysql table with already-existing primary key

Question

I have a mysql table like this:

PK    other_id    name      url
1         1       jeff      www.jeff.com
2         2       steve     www.steve.com
3         3       jenn      www.jenn.com
4         4       susan     www.susan.com
5         5       melissa   www.melissa.com

I need to enter a new row into the table that has primary key 3. This primary key is already taken so I must somehow split the able, insert the new row for the new PK=3, then attach the remaining rows which will now start from PK=4.

PK    other_id    name      url
1         1       jeff      www.jeff.com
2         2       steve     www.steve.com
3         3       josh      www.josh.com  <---- new row added with PK=3
4         4       jenn      www.jenn.com
5         5       susan     www.susan.com
6         6       melissa   www.melissa.com

I tried alter table techniques but have had no success.

Why you would do that? Techical keys are only unique numbers without any symantic issue. — Jens
Why would you want to do that? That's the role of the PK, to be a stable reference to a record. If you are going to shift the PK down you need to take into account any other entries in the DB that reference that PK field. Also by doing this any new insert on that table and applying your logic will kill the database server in the very near future. — AlexL
The application depends on the ordering in the table as it is being streamed in. It reads in a serial fashion. — Cybernetic
Then modify the select statement of the application to order it in the desired order and use another way then shifting down rows to determine the order. — AlexL
I spuupose you have some really unique requirements but generally this does not make any sense. If you want to see the rows in a particular order, then add an "order" column with an index and set that when you insert a new row. This way you don't have to change the PK, which you probably use for relations, so that if you change it (on many records) you will have to change it (on many records) in other tables too! — mshthn

Adam Adam · Accepted Answer · 2015-02-16T19:40:18

You can do the following

INSERT INTO mytable VALUES(....);
SELECT @ID:=LAST_INSERT_ID();
UPDATE mytable SET PK=PK+1, other_id=other_id+1 WHERE PK>=3;
UPDATE mytable SET PK=3, other_id=3 WHERE PK=@ID;

Insert new row into mysql table with already-existing primary key

2 Answers