20 外键

使用外键连接表，并选择合适的更新与删除动作。

一个外键是一种约束，它规定“此列中的值必须已经作为某个其他表中的主键（或唯一键）存在”。关系型数据库正是通过它来实施引用完整性的——你不能让子行指向不存在的父行，从而成为孤儿行；（取决于你选择的规则）数据库还可以级联或限制对父行的更新。凡是涉及多个表的关系模型，都需要外键。

本章要点

使用 REFERENCES 声明外键
ON DELETE 和 ON UPDATE 动作：NO ACTION、RESTRICT、CASCADE、SET NULL、SET DEFAULT
为什么在外键列上建立索引几乎总是个好主意
DEFERRABLE 约束如何让你在一个事务中批量执行变更
检查系统目录中的外键

示例

示例 20.1 外键

-- Parent table
DROP TABLE IF EXISTS comments_example;
DROP TABLE IF EXISTS authors_example;

CREATE TABLE authors_example (
  id   integer PRIMARY KEY,
  name text NOT NULL
);

-- Child table referencing parent
CREATE TABLE comments_example (
  id        integer PRIMARY KEY,
  author_id integer NOT NULL REFERENCES authors_example(id) ON DELETE CASCADE,
  body      text NOT NULL,
  parent_id integer REFERENCES comments_example(id) ON DELETE SET NULL  -- self-reference!
);

-- Index the FK column so joins and cascading deletes don't seq-scan
CREATE INDEX ON comments_example (author_id);
CREATE INDEX ON comments_example (parent_id);

INSERT INTO authors_example VALUES (1, 'Alice'), (2, 'Bob');
INSERT INTO comments_example (id, author_id, body) VALUES
  (1, 1, 'First!'),
  (2, 2, 'Reply'),
  (3, 1, 'Nice');
UPDATE comments_example SET parent_id = 1 WHERE id = 2;

-- Refused: author 99 does not exist
-- INSERT INTO comments_example (id, author_id, body) VALUES (4, 99, 'orphan');

-- Cascade: deleting Alice removes her comments (and Bob's reply loses its parent_id)
DELETE FROM authors_example WHERE id = 1;
SELECT * FROM comments_example ORDER BY id;

-- Inspect foreign keys
SELECT conname, pg_get_constraintdef(oid) AS def
FROM pg_constraint
WHERE contype = 'f' AND conrelid = 'comments_example'::regclass;

REFERENCES other_table(column) 用于声明外键。被引用的列必须是另一个表的主键或唯一约束。如果省略列名，就假定引用该表的主键：在这里，REFERENCES authors_example 等价于 REFERENCES authors_example(id)。

当父行被删除，或它的键发生变化时，ON DELETE 和 ON UPDATE 动作决定会发生什么：

NO ACTION（默认）：如果仍有任何子行引用该父行，就报错；在语句结束时检查。
RESTRICT：类似 NO ACTION，但会立即检查（不能延迟）。
CASCADE：同时删除或更新子行。如果子行属于父行，这很有用。
SET NULL：将子表中的列设为 NULL（该列必须允许空值）。
SET DEFAULT：将子表中的列设为其默认值。

每条 INSERT/UPDATE/DELETE 都会检查外键——它们并非没有代价。外键也是模式中最重要的正确性保障之一；与避免数据损坏相比，这项代价几乎总是值得的。

运行示例

运行方式：

$ psql -f source/foreign-keys.sql postgres
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX
INSERT 0 2
INSERT 0 3
UPDATE 1
DELETE 1
 id | author_id | body  | parent_id
----+-----------+-------+-----------
  2 |         2 | Reply |
(1 row)
 ...

常见陷阱

忘记在外键列上建立索引：删除父行时，PostgreSQL 必须检查（并可能对其子行执行级联操作）；如果外键列没有索引，这会执行一次顺序扫描。务必给外键列建立索引。
过于轻率地选择 CASCADE：生产环境中的一次删除可能级联到数百万行。请审查你使用的 ON DELETE CASCADE 规则，尤其是面向用户的表。
导入存在跨表依赖的数据：先装载父表，再装载子表。或者使用 DEFERRABLE INITIALLY DEFERRED，让外键检查在提交时进行，而不是逐行进行。
外键之间的循环依赖要求使用可延迟约束。PostgreSQL 支持这种约束，但如果可能，设计时应围绕这一需求安排结构。

提示

DEFERRABLE INITIALLY DEFERRED 会让约束检查在 COMMIT 时进行，而不是在语句结束时进行。结合 SET CONSTRAINTS ALL DEFERRED，你就可以在一个事务中进行多项相互关联的变更，而不必把它们精确地按顺序排列。

动手练习

尝试插入一条作者不存在的评论：INSERT INTO comments_example (id, author_id, body) VALUES (10, 99, 'orphan');，观察违反外键约束的错误。然后添加第二位作者和一条评论，并对该作者执行 DELETE，观察 ON DELETE CASCADE 的实际效果。

20 外键

本章要点

示例

运行示例

常见陷阱

下载