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.
If you need to change PK you could use DEFFERED CONSTRAINTS:
SET CONSTRAINTS sets the behavior of constraint checking within the current transaction. IMMEDIATE constraints are checked at the end of each statement. DEFERRED constraints are not checked until transaction commit. Each constraint has its own IMMEDIATE or DEFERRED mode.
Data preparation:
CREATE TABLE master(master_id VARCHAR(10) PRIMARY KEY, name VARCHAR(10));
INSERT INTO master(master_id, name) VALUES ('foo', 'bar');
CREATE TABLE detail(detail_id INT PRIMARY KEY, master_id VARCHAR(10)
,name VARCHAR(10)
,CONSTRAINT fk_det_mas FOREIGN KEY (master_id) REFERENCES master(master_id));
INSERT INTO detail(detail_id, master_id, name) VALUES (1234,'foo','blu');
In normal situtation if you try to change master detail you will end up with error:
update detail set master_id='foo2' where master_id='foo';
-- ERROR: insert or update on table "detail" violates foreign key
-- constraint "fk_det_mas"
-- DETAIL: Key (master_id)=(foo2) is not present in table "master"
update master set master_id='foo2' where master_id='foo';
-- ERROR: update or delete on table "master" violates foreign key
-- constraint "fk_det_mas" on table "detail"
-- DETAIL: Key (master_id)=(foo) is still referenced from table "detail".
But if you change FK resolution to deffered, there is no problem:
ALTER TABLE detail DROP CONSTRAINT fk_det_mas ;
ALTER TABLE detail ADD CONSTRAINT fk_det_mas FOREIGN KEY (master_id)
REFERENCES master(master_id) DEFERRABLE;
BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
UPDATE master set master_id='foo2' where master_id = 'foo';
UPDATE detail set master_id='foo2' where master_id = 'foo';
COMMIT;
Please note that you could do many things inside transaction, but during COMMIT all referential integrity checks have to hold.
If you want to automate this process you could use dynamic SQL and metadata tables. Here Proof of Concept for one FK column:
CREATE TABLE master(master_id VARCHAR(10) PRIMARY KEY, name VARCHAR(10));
INSERT INTO master(master_id, name)
VALUES ('foo', 'bar');
CREATE TABLE detail(detail_id INT PRIMARY KEY, master_id VARCHAR(10),
name VARCHAR(10)
,CONSTRAINT fk_det_mas FOREIGN KEY (master_id)
REFERENCES master(master_id)DEFERRABLE ) ;
INSERT INTO detail(detail_id, master_id, name) VALUES (1234,'foo','blu');
CREATE TABLE detail_second(detail_id INT PRIMARY KEY, name VARCHAR(10),
master_id_second_name VARCHAR(10)
,CONSTRAINT fk_det_mas_2 FOREIGN KEY (master_id_second_name)
REFERENCES master(master_id)DEFERRABLE ) ;
INSERT INTO detail_second(detail_id, master_id_second_name, name)
VALUES (1234,'foo','blu');
And code:
BEGIN TRANSACTION;
SET CONSTRAINTS ALL DEFERRED;
DO $$
DECLARE
old_pk TEXT = 'foo';
new_pk TEXT = 'foo2';
table_name TEXT = 'master';
BEGIN
-- update childs
EXECUTE (select
string_agg(FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'' ;'
,c.relname,pa.attname, new_pk,pa.attname, old_pk),CHR(13)) AS sql
from pg_constraint pc
join pg_class c on pc.conrelid = c.oid
join pg_attribute pa ON pc.conkey[1] = pa.attnum
and pa.attrelid = pc.conrelid
join pg_attribute pa2 ON pc.confkey[1] = pa2.attnum
and pa2.attrelid = table_name::regclass
where pc.contype = 'f');
-- update parent
EXECUTE ( SELECT FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'';'
,c.relname,pa.attname, new_pk,pa.attname, old_pk)
FROM pg_constraint pc
join pg_class c on pc.conrelid = c.oid
join pg_attribute pa ON pc.conkey[1] = pa.attnum
and pa.attrelid = pc.conrelid
WHERE pc.contype IN ('p','u')
AND conrelid = table_name::regclass
);
END
$$;
COMMIT;
I tried it, but it does not work. It would be nice, if the script could show the SQL. This is enough. After looking at the generated SQL I can execute it if psql -f
have you tried it? It did not work for me.
Yes, I have tried it. Just check above live demo links. I prepare the same demo with more debug info:
Please make sure that FKs are defined as DEFFERED.
Then I wanted to see the sql instead of executing it. I removed "perform" from your fiddle, but then I get an error. See: http://dbfiddle.uk/?rdbms=postgres_10&fiddle=b9431c8608e54b4c42b5dbd145aa1458
If you only want to get SQL code you could create function:
CREATE FUNCTION generate_update_sql(table_name VARCHAR(100), old_pk VARCHAR(100), new_pk VARCHAR(100))
RETURNS TEXT
AS
$$
BEGIN
RETURN
-- update childs
(SELECT
string_agg(FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'' ;', c.relname,pa.attname, new_pk,pa.attname, old_pk),CHR(13)) AS sql
FROM pg_constraint pc
JOIN pg_class c on pc.conrelid = c.oid
JOIN pg_attribute pa ON pc.conkey[1] = pa.attnum and pa.attrelid = pc.conrelid
JOIN pg_attribute pa2 ON pc.confkey[1] = pa2.attnum and pa2.attrelid = table_name::regclass
WHERE pc.contype = 'f') || CHR(13) ||
-- update parent
(SELECT FORMAT('UPDATE %s SET %s = ''%s'' WHERE %s =''%s'';', c.relname,pa.attname, new_pk,pa.attname, old_pk)
FROM pg_constraint pc
JOIN pg_class c on pc.conrelid = c.oid
JOIN pg_attribute pa ON pc.conkey[1] = pa.attnum and pa.attrelid = pc.conrelid
WHERE pc.contype IN ('p','u')
AND conrelid = table_name::regclass)
;
END
$$ LANGUAGE plpgsql;
And execution:
SELECT generate_update_sql('master', 'foo', 'foo');
UPDATE detail SET master_id = 'foo' WHERE master_id ='foo' ;
UPDATE detail_second SET master_id_second_name = 'foo'
WHERE master_id_second_name ='foo' ;
UPDATE master SET master_id = 'foo' WHERE master_id ='foo';
Of course there is a place for improvement for example handling identifiers like "table with space in name" and so on.