Menu

PostgreSQL 外键与 ON DELETE CASCADE

外键让一列只接受另一张表中存在的值。内容包括 REFERENCES、ON DELETE CASCADE、SET NULL 和 RESTRICT,给已有的表添加外键,以及为外键建索引。

本页包含可运行的编辑器。编辑、运行,立即查看输出。

外键把一列和另一张表的主键或唯一列绑定在一起。如果那边没有匹配的行,PostgreSQL 就会拒绝这个值。写法是 column type REFERENCES other_table (column)。加上 ON DELETE CASCADE 或 ON DELETE SET NULL,说明删除父行时子行怎么处理。

被引用的列必须有 PRIMARY KEY 或 UNIQUE 约束。如果写 REFERENCES customers 而不写列,PostgreSQL 会使用 customers 的主键。

违反外键约束时会怎样

为不存在的客户下的订单会被拒绝:

错误信息中会写出约束的名字 orders_customer_id_fkey,这是 PostgreSQL 给未命名外键起的名字。删除仍有订单的客户也会以同样的方式失败,除非外键另有规定。

ON DELETE 选项

选项删除父行时
NO ACTION(默认)只要还有子行,删除就失败
RESTRICT同上,但检查不能推迟到事务结束时
CASCADE子行也一起删除
SET NULL子行的外键列变成 NULL
SET DEFAULT子行的这一列变成它的默认值

ON UPDATE 接受同样的选项。它在父行的键值改变时生效,而使用自动生成的 id 时,这种情况很少见。

CASCADE 适合那些离开父行就没有意义的行,比如订单的明细行:

只剩下 chair。级联会沿着更多的外键继续,所以一次删除可能会删掉好几张表中的行。

SET NULL 保留子行,只是解除关联:

Ada 留了下来,team_id 被设为 NULL。从 PostgreSQL 15 开始,ON DELETE SET NULL (column) 可以只清空多列外键中的部分列。

给已有的表添加外键

ALTER TABLE orders
    ADD CONSTRAINT orders_customer_id_fkey
    FOREIGN KEY (customer_id) REFERENCES customers (id) ON DELETE CASCADE;

这会检查每一个已有的行,只要有一行没有父行就会失败。对于大表,先用 NOT VALID 添加外键,这样只检查新行。然后运行 ALTER TABLE orders VALIDATE CONSTRAINT orders_customer_id_fkey;,在不阻塞写入的情况下检查旧行。要修改已有外键的 ON DELETE 行为,在同一条 ALTER TABLE 中删除约束再重新添加。

给外键列建索引

PostgreSQL 会为被引用的一方建索引,但不会为引用方的列建索引。如果 orders.customer_id 上没有索引,每次从 customers 删除一行时都要扫描整张 orders 来查找子行,查询某位客户的订单时也一样:

CREATE INDEX orders_customer_id_idx ON orders (customer_id);

要列出一张表的外键,查询 pg_constraint:

如何基于这些外键做连接,见连接页面;完整规则(包括可延迟的外键)见手册的约束章节。

常见问题

PostgreSQL 会为外键自动创建索引吗?

不会在引用方的列上创建。被引用的列已经有索引,因为它必须是主键或唯一列。外键列上的索引要自己建,因为连接和从父表删除时都要搜索这一列。

PostgreSQL 中怎么删除外键?

ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey;。没有命名的外键叫做 table_column_fkey;在 psql 中用 \d orders 可以看到准确的名字。

PostgreSQL 中外键列可以是 NULL 吗?

可以。外键列中的 NULL 不会被检查,所以这一行不需要父行。如果每一行都必须有父行,就加上 NOT NULL。

外键可以引用同一张表吗?

可以。像 manager_id int REFERENCES employees (id) 这样的列指向同一张表中的另一行。组织架构图、评论楼层这类树形结构就是这样存储的。

Coddy 编程语言插图

和 Coddy 一起学编程

开始