Generate SQL to update primary key

sql postgresql introspection
guettli picture guettli · Aug 16, 2013 · Viewed 15.4k times · Source

I want to change a primary key and all table rows which reference to this value.

# table master
master_id|name
===============
foo|bar

# table detail
detail_id|master_id|name
========================
1234|foo|blu

If I give a script or function 

 table=master, value-old=foo, value-new=abc

I want to create a SQL snippet that executes updates on all tables which refere to table "master":

update detail set master_id=value-new where master_id=value-new;
.....

With the help of introspection, this should be possible.

I use postgres.

Update

The problem is, that there are many tables which have a foreign-key to the table "master". I want a way to automatically update all tables which have a foreign-key to master table.

Answer

Craig Ringer picture Craig Ringer · Aug 16, 2013

The easiest way to deal with primary key changes - by far - is to ALTER your referring foreign key constraints to be ON UPDATE CASCADE.

You are then free to update the primary key values, and the changes will cascade to child tables. It can be a very slow process due to all the random I/O, but it will work.

You do need to watch out not to violate uniqueness constraints on the primary key column during the process.

A fiddlier but faster way is to add a new UNIQUE column for the new PK, populate it, add new columns to all the referring tables that point to the new PK, drop the old FK constraints and columns, then finally drop the old PK.

Related questions

Select first row in each GROUP BY group?

As the title suggests, I'd like to select the first row of each set of rows grouped with a GROUP BY. Specifically, if I've got a purchases table that looks like this: SELECT * FROM purchases; My Output: id | customer | total …

Read More
How do I (or can I) SELECT DISTINCT on multiple columns?

I need to retrieve all rows from a table where 2 columns combined are all different. So I want all the sales that do not have any other sales that happened on the same day for the same price. The sales …

Read More
How to select the nth row in a SQL database table?

I'm interested in learning some (ideally) database agnostic ways of selecting the nth row from a database table. It would also be interesting to see how this can be achieved using the native functionality of the following databases: SQL Server …

Read More