外键把一列和另一张表的主键或唯一列绑定在一起。如果那边没有匹配的行,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) 这样的列指向同一张表中的另一行。组织架构图、评论楼层这类树形结构就是这样存储的。