SQLite 外键：REFERENCES、ON DELETE 与 PRAGMA

外键是表与表之间的指针

外键是一张表中的一列，它的值必须与另一张表中的某一行匹配。关系型数据库正是用它来表达“这条 posts 记录属于那条 authors 记录”，而不必把作者的名字和邮箱复制到每篇文章里。

下面是一个最小的例子：一张父表和一张子表，通过外键连接起来：

PRAGMA foreign_keys = ON;

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

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO authors (name) VALUES ('Ada'), ('Boris');
INSERT INTO posts (title, author_id) VALUES ('Hello', 1), ('Notes', 2);

SELECT p.title, a.name
FROM posts p JOIN authors a ON a.id = p.author_id;

author_id INTEGER REFERENCES authors(id) 就是完整的外键声明。它的意思是：这一列存放的是 authors 表中的某个 id。数据库现在知道这两张表有关联；如果开启了外键检查，它会拒绝指向不存在作者的插入。

外键默认是关闭的

这是关于 SQLite 外键最重要的一点，也几乎让每个人都吃过惊：SQLite 会解析 REFERENCES 子句，但除非你主动要求，否则不会强制执行。原因是历史兼容性：很多旧数据库是在这个功能出现之前建立的。

看看不开启检查时会发生什么：

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

-- author 999 does not exist, but SQLite happily accepts this:
INSERT INTO posts (title, author_id) VALUES ('Orphan', 999);

SELECT * FROM posts;

这条孤儿行直接插进去了。要得到你真正想要的保护，在每个连接开始时运行 PRAGMA foreign_keys = ON;：

PRAGMA foreign_keys = ON;

CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    title     TEXT,
    author_id INTEGER REFERENCES authors(id)
);

INSERT INTO posts (title, author_id) VALUES ('Orphan', 999);

现在这次插入会失败，报错 FOREIGN KEY constraint failed。这个 pragma 按连接生效，而不是按数据库生效：设置并不会保存在文件里。每个应用、每次命令行会话、每个测试夹具都必须自己设置。大多数生产代码会在打开连接后立即运行 PRAGMA foreign_keys = ON;。

REFERENCES 子句的真正要求

被引用的列必须是 PRIMARY KEY，或者带有 UNIQUE 约束。这样 SQLite 才能保证查找结果是唯一的。两边的类型也应该兼容：SQLite 对类型很宽松，但混用类型就是在自找麻烦。

外键有两种写法。一种是直接写在列上：

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

CREATE TABLE posts (
    id        INTEGER PRIMARY KEY,
    author_id INTEGER REFERENCES authors(id),
    title     TEXT
);

SELECT sql FROM sqlite_master WHERE name = 'posts';

另一种是写成单独的表级约束，当外键涉及多列时必须用这种写法：

CREATE TABLE posts (id INTEGER PRIMARY KEY, title TEXT);
CREATE TABLE tags  (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL,
    tag_id  INTEGER NOT NULL,
    PRIMARY KEY (post_id, tag_id),
    FOREIGN KEY (post_id) REFERENCES posts(id),
    FOREIGN KEY (tag_id)  REFERENCES tags(id)
);

SELECT sql FROM sqlite_master WHERE name = 'post_tags';

两种写法产生的约束完全相同。哪种对当前的表更易读，就用哪种。

ON DELETE：子行会怎样

删除父行时，SQLite 必须决定如何处理指向它的子行。你用 ON DELETE 来指定策略：

PRAGMA foreign_keys = ON;

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

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

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('A'), ('B') ON CONFLICT DO NOTHING;
INSERT INTO posts (title, author_id) VALUES ('A', 1), ('B', 1);

DELETE FROM authors WHERE id = 1;
SELECT * FROM posts;

删除 Ada 的同时也删除了她的两篇文章。可选的策略有：

• CASCADE：把子行一起删除。适合“从属”数据，比如作者名下的文章、订单里的商品。
• SET NULL：把外键列置为 NULL。适合子行在没有父行时也应保留的情况（例如已删除用户的评论变成匿名评论）。
• SET DEFAULT：把外键列设为它声明的默认值。
• RESTRICT：只要还有子行就阻止删除。在语句执行时立即失败。
• NO ACTION：默认值。多数情况下效果与 RESTRICT 类似（它把检查推迟到提交时，但结果一样：你不能留下悬空的子行）。

ON UPDATE 对父行键值的修改也是同样的工作方式，不过修改主键的情况很少见。

Foreign Key Constraint Failed 是什么意思

你会在两种情况下看到这个错误。第一种是插入或更新的子行，其值没有匹配的父行：

sqlite> INSERT INTO posts (title, author_id) VALUES ('Stray', 999);
Runtime error: FOREIGN KEY constraint failed

要么作者 999 不存在，要么你把列的类型弄混了。先插入父行，或者修正这个值。

第二种是当外键使用 RESTRICT 或 NO ACTION 时，删除（或更新）一个仍有子行的父行：

sqlite> DELETE FROM authors WHERE id = 1;
Runtime error: FOREIGN KEY constraint failed

要么先删除子行，要么如果你确实想要级联效果，就把外键改成 ON DELETE CASCADE/SET NULL。

还有一个不太常见的“近亲”：FOREIGN KEY mismatch。当被引用的列既不是主键也不唯一，或者列的数量对不上时，就会触发它。这是表结构的错误，而不是数据的错误。

给已有的表添加外键

SQLite 的 ALTER TABLE 功能有限：你可以添加一个带外键的新列，但不能给已经存在的列补上外键。标准的变通办法是“重建再改名”：

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

INSERT INTO authors (id, name) VALUES (1, 'Ada');
INSERT INTO posts (title, author_id) VALUES ('Hello', 1), ('World', 1);

PRAGMA foreign_keys = OFF;
BEGIN;

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

INSERT INTO posts_new SELECT id, title, author_id FROM posts;

DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

COMMIT;
PRAGMA foreign_keys = ON;

SELECT sql FROM sqlite_master WHERE name = 'posts';

步骤是：关闭外键检查，用你想要的约束创建新表，复制数据，删除旧表，再改名。BEGIN/COMMIT 保证整个过程是原子的。最后重新开启检查，SQLite 会用新的约束检查所有已有的行：如果有无效数据，事务已经提交了，所以如果你担心的话，最好事先检查。

迁移完成后运行 PRAGMA foreign_key_check;，确认没有孤儿行。

一个真实的表结构

把这些组合起来：一个小型博客的表结构，包含父表、子表，以及一张用于多对多标签的关联表：

PRAGMA foreign_keys = ON;

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

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

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

CREATE TABLE post_tags (
    post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id  INTEGER NOT NULL REFERENCES tags(id)  ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

有三点值得注意。author_id 是 NOT NULL：每篇文章都必须有作者。posts → authors 的外键是级联的，所以删除作者会清除他的所有文章。post_tags 关联表两边都是级联的，所以无论删除文章还是标签，关联行都会自动清理干净。

能帮你少踩坑的习惯

• 在每个连接上设置 PRAGMA foreign_keys = ON;。把它写进应用打开数据库的流程里，而不是靠自己记着。
• 在外键列上加一个索引。SQLite 会自动为父表的键建立索引，但不会为子表的外键列建立索引，而每次删除父行时，ON DELETE CASCADE 都要在子表中查找一次。
• 有意识地选择 ON DELETE。默认值（NO ACTION）很安全，但意味着你每次想清理数据时都会撞上“constraint failed”。想清楚应该发生什么，然后把它声明出来。
• 迁移或批量导入之后运行 PRAGMA foreign_key_check;，在孤儿行变成 bug 之前把它们找出来。

下一篇：INNER JOIN

外键描述的是表之间的关系，而连接才是你跨表查询的方式。下一页介绍 INNER JOIN：把相关表中的行组合起来，并从每张表中取回你想要的列。