Kodokon kodokon.com

外键与引用完整性

用 REFERENCES 保证你的表之间的一致性，并有意识地选择 ON DELETE 的行为。

8 分钟 · 3 题

在 Kodokon 中打开本课

一个外键声明某一列指向另一张表的主键。此后数据库会拒绝任何孤立的值：你无法创建一本作者并不存在的书。请注意，在 SQLite 中这项检查默认是关闭的：请在脚本开头用一条 PRAGMA 来启用它。

SQL
PRAGMA foreign_keys = ON;

CREATE TABLE authors (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  author_id INTEGER NOT NULL
    REFERENCES authors(id)
    ON DELETE CASCADE
);

INSERT INTO authors (id, name) VALUES
  (1, 'Ursula K. Le Guin'),
  (2, 'Ted Chiang');

INSERT INTO books (id, title, author_id) VALUES
  (1, 'A Wizard of Earthsea', 1),
  (2, 'The Dispossessed', 1),
  (3, 'Exhalation', 2);
REFERENCES 把 books.author_id 关联到 authors.id。

测试这项保护：插入一本孤立的书，然后删除一位作者。第一条语句会被拒绝；而第二条则借助 ON DELETE CASCADE 把它的书一并带走。

SQL
INSERT INTO books (id, title, author_id)
VALUES (4, 'Orphan', 99);

DELETE FROM authors WHERE id = 1;

SELECT id, title FROM books;
插入失败；在 DELETE 之后，只剩下 Exhalation。

ON DELETE 定义了当父行消失时子行的命运。CASCADE 会把它们一并删除。SET NULL 会保留该行但清空引用 - 因此该列必须允许 NULL。若不写任何子句，SQLite 会采用 NO ACTION：只要还有子行存在，删除父行就会被拒绝，这种行为接近 RESTRICT。

SQL
CREATE TABLE articles (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  reviewer_id INTEGER
    REFERENCES authors(id)
    ON DELETE SET NULL
);
可选的关联：如果审稿人离开了，文章依然保留。

知识检测

确认你已牢记本课的重点内容。

  1. 在 books.author_id 上设置了 ON DELETE CASCADE 时，DELETE FROM authors WHERE id = 1 会导致什么？
    • 只要该作者还有书就会报错
    • 删除该作者以及他所有的书
    • 把他那些书的 author_id 置为 NULL
  2. 为什么要在 SQLite 中运行 PRAGMA foreign_keys = ON;？
    • 为了创建外键
    • 因为外键检查默认是关闭的，而且是逐个连接而言的
    • 为了加快连接的速度
  3. reviewer_id 列必须满足什么条件才能使用 ON DELETE SET NULL？
    • 被声明为 UNIQUE
    • 是 INTEGER 类型
    • 允许 NULL，因此不能带 NOT NULL