13. 外键与参照完整性

到现在我们的 users 和 orders 还是两张互不相关的表。但现实里它们是有关系的：一笔订单 orders 必须属于某个存在的用户 users。这种”表与表之间的所属/引用关系”，在关系型数据库里用外键（foreign key）来表达；它保证的数据一致性，叫做参照完整性（referential integrity）。比如：不能插入一笔”属于用户 999”的订单（因为根本没有 999 号用户），也不能在还有订单挂着的时候把那个用户删掉——否则就留下”孤儿订单”。

为什么需要外键

没有外键时，你可以随心所欲地往 orders.user_id 里塞任何数字，哪怕对应的用户根本不存在；也可以把用户删光，留下一堆指向虚空订单。应用迟早在这些”断链”数据上崩溃或算出错。外键就是数据库层面的”硬保证”：插入子表前先确认父表里有对应行；动父表前先确认没有子表行还指着它。把校验下沉到数据库，比在每段应用代码里手工判断可靠得多。

语法：父表、子表、父键、子键

先把全局示例表建好。users 是父表（被引用方），orders 是子表（引用方），orders.user_id 指向 users.id：

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

CREATE TABLE orders(
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER,
  amount     REAL,
  created_at TEXT,
  FOREIGN KEY(user_id) REFERENCES users(id)
);

术语对照（务必记住，后面报错信息里会用到）：

• 父表（parent table）：被引用的表，这里是 users。
• 子表（child table）：写外键的表，这里是 orders。
• 父键（parent key）：父表里被指的那列，通常是它的主键，这里是 users.id。
• 子键（child key）：子表里负责”指过去”的那列，这里是 orders.user_id。

外键可以写在列定义后（简写 user_id INTEGER REFERENCES users(id)），也可以像上面这样写成独立的表级 FOREIGN KEY(...) REFERENCES ...。多列组合外键用表级写法：FOREIGN KEY(a, b) REFERENCES 父表(x, y)。

sqlite> PRAGMA foreign_keys = ON;
sqlite> PRAGMA foreign_keys;
1

PRAGMA foreign_keys 返回 1 表示已开、0 表示关。命令行里每次新开连接都要重新开一次——它不持久保存在数据库文件里。

外键生效时会发生什么

先放一个真实存在的用户，再下合法订单：

sqlite> INSERT INTO users(name, email) VALUES('张三','a@b.com');
sqlite> INSERT INTO orders(user_id, amount, created_at) VALUES(1, 99.5, '2026-07-31');
sqlite> -- 成功，因为 user_id=1 在 users 里存在

试图插入一个指向不存在用户的订单：

sqlite> INSERT INTO orders(user_id, amount, created_at) VALUES(999, 10.0, '2026-07-31');
Error: FOREIGN KEY constraint failed

试图删除一个还有订单挂着的用户：

sqlite> DELETE FROM users WHERE id = 1;
Error: FOREIGN KEY constraint failed

必须先清掉该用户的订单，才能删用户——这就是参照完整性在工作。注意一个细节：如果 user_id 是 NULL，外键不强制要求对应父行存在（NULL 表示”暂时不知道归属”）。若想连 NULL 也禁止，给 user_id 加 NOT NULL。

ON DELETE / ON UPDATE：父表变动时子表怎么办

光是”拦住”有时不够，你可能想：删用户时，把他所有订单也一起删掉。这通过外键的 ON DELETE / ON UPDATE 动作配置。可选动作有五种：

• NO ACTION（默认）：父表被删/改时，若有子行指着它，直接报错拒绝。
• RESTRICT：和 NO ACTION 类似，但更”即时”，一动手就拦。
• CASCADE：级联。删父行时把指着的子行也删；改父键时把子键同步改。
• SET NULL：把指着的子行的外键列设为 NULL。
• SET DEFAULT：把子行的外键列设为该列的默认值。

最常用的是 ON DELETE CASCADE（删用户连带删订单）和 ON DELETE SET NULL（删用户时订单保留但归属置空）。写法示例：

CREATE TABLE orders(
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER,
  amount     REAL,
  created_at TEXT,
  FOREIGN KEY(user_id) REFERENCES users(id)
    ON DELETE CASCADE
    ON UPDATE NO ACTION
);

这样删 users 里 id=1 时，orders 中所有 user_id=1 的行会被自动跟着删掉，不再报 FOREIGN KEY 错误。

再用 ON DELETE SET NULL 体会一下”保留数据”的思路：订单不能随用户消失，宁愿保留订单、只把归属置空。

CREATE TABLE orders(
  id         INTEGER PRIMARY KEY,
  user_id    INTEGER,
  amount     REAL,
  created_at TEXT,
  FOREIGN KEY(user_id) REFERENCES users(id)
    ON DELETE SET NULL
);

删掉用户后，他名下的订单不会被删，只是 user_id 变成 NULL——这些订单成了”归属待定”的孤儿，但数据没有丢，后续可派给”未知用户”或重新指派。再辨析 NO ACTION 与 RESTRICT 的细微差别：两者都会拦下破坏参照完整性的操作，但时机不同——RESTRICT 在父键被改动的”瞬间”就拦下，不等语句结束；NO ACTION 等到整条语句执行完才检查。绝大多数业务里二者效果一致，用默认的 NO ACTION 即可；只有当你确实需要”刚动手就报错”的即时性时才选 RESTRICT。

性能提示：给子键加索引

每次删父行或改父键，SQLite 都要去子表里翻”有没有行指着它”，如果子键 user_id 上没有索引，就得全表扫，大表会很慢。所以实战中几乎总给外键列建索引：

CREATE INDEX idx_orders_user_id ON orders(user_id);

这个索引不强制，但强烈建议——它和”写放大”权衡后通常利大于弊。

事务里的一个小限制

不能在一条多语句事务进行到一半时开关外键（非自动提交模式下 PRAGMA foreign_keys 不生效，也不会报错，只是没作用）。所以开启动作放在连接刚建立、任何事务之前最稳妥。

类比小结

把外键想成”户口本上的亲属关系”：子表 orders 是小孩，父表 users 是家长；外键说”每个小孩必须有个真实存在的家长”。但 SQLite 这位”民政局”默认偷懒不上这层校验（默认 OFF），你得每次开门营业前喊一声 PRAGMA foreign_keys = ON 它才肯查。级联动作 ON DELETE CASCADE 相当于”家长注销户口，小孩一并迁出”；SET NULL 则是”家长没了，小孩归属栏留空”。把关系交给数据库守，比自己在代码里到处写 if 存在 可靠得多。