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.

@L_404_3@

最后编辑

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;

执行：

SELECT generate_update_sql('master','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 Function Demo

当然,还有一个需要改进的地方,例如处理标识符,例如“名称中包含空格的表格”等.