Menu
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.
735
You can modify a primary key in SQL Server by using SQL Server Management Studio or Transact-SQL. You can modify the primary key of a table by changing the column order, index name, clustered option, or fill factor.
Because a table can have only one primary key, you cannot add a primary key to a table that already has a primary key defined. To change the primary key of a table, delete the existing key using a DROP clause in an ALTER TABLE statement and add the new primary key.
SQL UPDATE SyntaxTo use the UPDATE method, you first determine which table you need to update with UPDATE table_name . After that, you write what kind of change you want to make to the record with the SET statement. Finally, you use a WHERE clause to select which records to change.
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.
94
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
