在POSTGRESQL中更新主键

Question

在POSTGRESQL中更新主键

7

我有一个来自以前项目的数据库，我想在另一个项目中使用它，由于安全原因，我需要更新其中一个表的ID。问题是该表被其他表的外键广泛引用：

CREATE TABLE "table_table" (
     "id" serial NOT NULL PRIMARY KEY,
     "created" timestamp with time zone NOT NULL,
);


CREATE TABLE "table_photo" (
     "id" serial NOT NULL PRIMARY KEY,
     "table_id" integer NOT NULL REFERENCES "table_table" ("id") DEFERRABLE INITIALLY DEFERRED,
);

现在，如果我更改table_table上的id，则来自table_photo的引用将无法工作。我可能会使用类似以下的内容来更改这些ID：

UPDATE table_table SET id = id + 15613;

我在某个地方看到过可以使用ON UPDATE CASCADE约束来完成这个操作，但我不太确定如何使用它。

顺便说一下：我正在使用Django ORM。

- Visgean Skeloru

1

很好，你包含了表定义。谢谢。 - Craig Ringer

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Craig Ringer · Accepted Answer

使用\d "table_photo"获取约束名，它显示：

Foreign-key constraints:
    "table_photo_table_id_fkey" FOREIGN KEY (table_id) REFERENCES table_table(id) DEFERRABLE INITIALLY DEFERRED

然后将其替换为带有 on update cascade 的约束条件：

ALTER TABLE "table_photo"
  DROP CONSTRAINT "table_photo_table_id_fkey",
  ADD CONSTRAINT "table_photo_table_id_fkey" 
    FOREIGN KEY ("table_id")
    REFERENCES "table_table"
    ON UPDATE CASCADE
    DEFERRABLE INITIALLY DEFERRED;

现在，当您执行UPDATE时，引用的行ID会自动更新。在"table_photo"."table_id"上添加索引将有很大帮助。

但是，对于大型表格而言，这可能会很慢。如果您有大型表格，则另一种选择是分成几个阶段进行操作。对于字段id被表B字段A_id所引用的表A:

向A添加一个新列new_id，并为其添加UNIQUE约束。保留其为空值。
向表B添加一个新列A_new_id，并对其施加外键约束，使其引用A(new_id)。
使用新值填充A.new_id
执行
```
UPDATE B
SET A_new_id = A.new_id 
FROM A
WHERE B.A_id = A.id; 
```
为了进行联合更新，将B.A_new_id的新ID值设置成相同的值。
删除列B.A_id并将B.A_new_id重命名为B.A_id。
删除列A.id并将A.new_id重命名为A.id。
在重命名后的A.id上创建一个PRIMARY KEY约束，使用之前自动创建的索引。

这个过程非常复杂，尤其是对于大表格而言，您通常希望分批完成每个步骤。

如果这显得太复杂，只需像上面那样使用级联外键约束即可。