Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
1.2k views
in Technique[技术] by (71.8m points)

postgresql - Generate SQL to update primary key

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.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Answer

0 votes
by (71.8m points)

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.


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome to OStack Knowledge Sharing Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...