生成用于更新主键的SQL语句。

Question

我想要修改一个主键以及所有引用该值的表格行。

# 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"表。我想要一种自动更新所有将外键指向主表的表的方法。

- guettli

---

为什么你想这样做？通常情况下，id的值应该是没有任何意义的，它只是一个唯一标识符。如果在分配了唯一标识符之后需要更改它，则表明您正在使用与一般SQL /关系数据库实践相反的模式（代码异味）。了解底层的功能需求可能会发现更合适的技术解决方案。 - MatBailie

Answer 1

处理主键更改最简单的方法 - 无疑是通过ALTER你的引用外键约束来使其成为ON UPDATE CASCADE。

然后，您可以自由地更新主键值，并且更改将自动传递到子表。由于所有随机I/O，它可能是一个非常缓慢的过程，但它会起作用。

在整个过程中，确保不违反主键列上的唯一性约束。

更麻烦但更快的方法是添加一个新的UNIQUE列用于新的 PK，填充数据，为所有指向新 PK 的引用表添加新列，删除旧的 FK 约束和列，最后删除旧的 PK。

- Craig Ringer

---

不好意思，这样做是可行的。但是我无法更改表模式（在我的环境中）。我只能更改表数据... - guettli

嗯，这是你想要首先提及的事情。那些规则实际上是愚蠢的；它们在“模式”和“数据”之间划分了一条人为而愚蠢的界线，而实际上这种区分比较模糊。静态查找表中的值是“模式”还是“数据”？尽管如此，如果你被限制在这种限制下，祝你好运，并与 information_schema 文档友好相处。 - Craig Ringer

这个答案包含人类可读的文本。我正在寻找可以被计算机执行的东西。 - guettli

Answer 2

如果需要更改主键，您可以使用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';
-- 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期间必须保持所有引用完整性检查。

编辑

如果您想要自动化此过程，可以使用动态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

编辑2:

我试过了，但是它不起作用。如果脚本可以显示SQL则会很好。查看生成的SQL后，我就能通过psql -f执行它了。你试过吗？对我来说不起作用。

是的，我试过了。只需检查上面的在线演示链接。 我准备了相同的演示，并提供了更多的调试信息：

• 之前的值
• 已执行的SQL
• 之后的值

请确保外键定义为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 
-- 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功能演示

当然，还有改进的空间，例如处理像“名称中带有空格的表”等标识符。

- Lukasz Szozda

---

我认为你的答案是正确的，但它并不符合问题。我更新了问题以强调我想要的内容。 - guettli

@guettli 好的，我明白你的意思了。我更新了我的答案。这只是概念验证，但我希望你会喜欢这个想法。你可以将它封装在存储过程/函数中，并将 pk_value 作为参数传递，而不是硬编码的值。 - Lukasz Szozda

我尝试过了，但它不起作用。如果脚本能够显示SQL语句就好了。这就足够了。在查看生成的SQL语句后，我可以使用psql -f来执行它。 - guettli

@guettli "你试过了吗？" 你有没有查看我的演示链接 DBFiddle Demo 2？正如我所说，这是一个概念验证，但它是有效的，否则我就不会发布它。我建议开始尝试编码。 - Lukasz Szozda

@m3asmi，我不确定我理解你的问题。该函数生成要执行的SQL查询。您可以使用第二种方法与EXECUTE（...）。 - Lukasz Szozda

Answer 3

我发现了一个不太优雅的解决方案：在 psql 中使用命令 \d master_table 可以显示相关信息。通过一些文本处理技巧，可以提取所需信息:

echo "UPDATE master_table SET id='NEW' WHERE id='OLD';" > tmp/foreign-keys.txt

psql -c '\d master_table' | grep -P 'TABLE.*CONSTRAINT.*FOREIGN KEY'  \
                                 >> tmp/foreign-keys.txt

reprec '.*TABLE ("[^"]*") CONSTRAINT[^(]*\(([^)]*)\).*' \
        "UPDATE \1 set \2='NEW' WHERE \2='OLD';" \
         tmp/foreign-keys.txt 

psql -1 -f tmp/foreign-keys.txt 

结果：

UPDATE "master_table" SET id='NEW' WHERE id='OLD';
UPDATE "other_table" SET master_id='NEW' WHERE master_id='OLD';
...

但更好的解决方案是受欢迎的。

- guettli

---

你可以从 pg_constraint 中获取外键，链接，然后创建一些动态 SQL。但这仍然是一个不太优雅的解决方案。 - Ihor Romanchenko

Answer 4

我认为你无法更新主键。一种可能的解决方法是从表列中删除主键约束，然后更新列值。

更新主键可能会导致严重的问题。但如果你仍然想这样做，请参考此线程。（kevchadders提供了一个解决方案。）

- Rahul Tripathi

---

我可以更新主键。我需要在一个事务中更新主表和所有带有外键的表。这个方法是可行的。但我想要自动化这个过程。我想确保我不会漏掉任何一张表。 - guettli

如果你错过了一个表，更新将失败并出现完整性错误，事务将回滚，因此您需要重试。问题在哪里？如果您经常这样做，那么您的数据库设计是疯狂的-如果主键可以更改，则外键应为“ON UPDATE CASCADE”，如果主键经常更改，则您的DB设计可能使用错误的东西作为主键。 - Craig Ringer

我的开发环境没有像生产服务器那样填充所有的表格。迁移可能在开发环境中运行成功，但在生产环境中可能会失败... - guettli

@guettli 如果你的开发环境和生产环境不同，而某些东西“在开发环境中可以工作，但在生产环境中可能会失败” - 你做错了事情，非常、非常的错误。 - Ihor Romanchenko

@IgorRomanchenko 大多数开发系统只有生产系统数据的子集。这就是为什么我不想猜测所有引用主表的表格。 - guettli