如果需要更改主键,您可以使用
DEFERRED CONSTRAINTS
:
SET CONSTRAINTS设置当前事务中约束检查的行为。 IMMEDIATE约束在每个语句结束时检查。 DEFERRED约束不会检查直到事务提交。 每个约束都有自己的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';
update master set master_id='foo2' where master_id='foo';
但是如果您将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
期间必须保持所有引用完整性检查。
编辑
如果您想要自动化此过程,可以使用动态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
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');
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
编辑2:
我试过了,但是它不起作用。如果脚本可以显示SQL则会很好。查看生成的SQL后,我就能通过psql -f执行它了。你试过吗?对我来说不起作用。
是的,我试过了。只需检查上面的在线演示链接。
我准备了相同的演示,并提供了更多的调试信息:
请确保外键定义为DEFERRED。
带有调试信息的DBFiddle 2
最后编辑
然后我想看SQL而不是执行它。我从你的fiddle中删除了"perform",但是我得到了一个错误。请参见: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
(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) ||
(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功能演示
当然,还有改进的空间,例如处理像“名称中带有空格的表”等标识符。
id
的值应该是没有任何意义的,它只是一个唯一标识符。如果在分配了唯一标识符之后需要更改它,则表明您正在使用与一般SQL /关系数据库实践相反的模式(代码异味)。了解底层的功能需求可能会发现更合适的技术解决方案。 - MatBailie