生成SQL以更新主键

我想更改主键和引用此值的所有表行.

# 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”的外键.我想要一种方法来自动更新所有具有外键到主表的表.

解决方法

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.

数据准备：

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分辨率改为deffer,则没有问题：

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 Demo

请注意,您可以在事务中执行许多操作,但在COMMIT期间,所有参照完整性检查都必须保留.

编辑

如果要自动执行此过程,可以使用动态SQL和元数据表.这里有一个FK专栏的概念证明：

CREATE TABLE master(master_id VARCHAR(10) PRIMARY KEY,name)
VALUES ('foo',CONSTRAINT  fk_det_mas FOREIGN KEY (master_id) 
   REFERENCES master(master_id)DEFERRABLE ) ;
INSERT INTO detail(detail_id,'blu');

CREATE TABLE detail_second(detail_id INT PRIMARY KEY,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,'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,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'';',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 Demo 2

编辑2：

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.

是的,我试过了.只需查看上面的现场演示链接.
我用更多的调试信息准备相同的演示：

>之前的价值观
>执行SQL
>之后的价值观

请确保将FK定义为DEFFERED.

DBFiddle 2 with debug info

最后编辑

Then I wanted to see the sql instead of executing it. I removed “perform” from your fiddle,but then I get an error. See: 07004

如果您只想获取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'' ;',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'';',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;

（编辑：湖南网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!