我想更改一个主键和所有引用此值的表行。
# table master master_id|name =============== foo|bar # table detail detail_id|master_id|name ======================== 1234|foo|blu
如果我提供脚本或功能
table=master, value-old=foo, value-new=abc
我想创建一个SQL代码段,该代码段在引用表“ master”的所有表上执行更新:
update detail set master_id=value-new where master_id=value-new; .....
在自省的帮助下,这应该是可能的。
我使用postgres。
更新
问题是,有许多表具有表“ master”的外键。我想要一种自动更新所有具有主表外键的表的方法。
如果您需要更改PK,可以使用DEFFEREDCONSTRAINTS:
DEFFEREDCONSTRAINTS
SET CONSTRAINTS设置当前事务中的约束检查行为。在每个语句的末尾检查IMMEDIATE约束。 在事务提交之前,不检查DEFERRED约束。 每个约束都有其自己的立即或延迟模式。
数据准备:
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');
在正常情况下,如果您尝试更改母版详细信息,则最终将出现错误:
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".
但是,如果将FK分辨率更改为递延,则没有问题:
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;
DBFiddle演示
请注意,您可以在事务内部做很多事情,但是在COMMIT所有引用完整性检查期间都必须保留。
COMMIT
如果要自动执行此过程,则可以使用动态SQL和元数据表。这里是FK专栏的概念证明:
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');
和代码:
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;
DBFiddle演示2
我试过了,但是没有用。如果脚本可以显示SQL,那就太好了。这就够了。查看生成的SQL之后,如果psql -f我可以执行它 你试过了吗?它对我没有用。
我试过了,但是没有用。如果脚本可以显示SQL,那就太好了。这就够了。查看生成的SQL之后,如果psql -f我可以执行它
你试过了吗?它对我没有用。
是的,我已经尝试过了。只需查看上面的实时演示链接。我准备了具有更多调试信息的相同演示:
请确保将FK定义为DEFFERED。
具有调试信息的DBFiddle2
然后,我想查看sql而不是执行它。我从您的小提琴中删除了“表演”,但随后出现错误。参见:http : //dbfiddle.uk/?rdbms=postgres_10&fiddle=b9431c8608e54b4c42b5dbd145aa1458
如果只想获取SQL代码,则可以创建函数:
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;
并执行:
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';
DBFiddle功能演示
当然,还有改进的地方,例如处理诸如“名称中有空格的表”之类的标识符,等等。