9.8 外键 · 波仔的 AI 编程实战课

📄 一句话概括

FOREIGN KEY (子列) REFERENCES 父表 (父列)，声明「我这一列的值必须是父表里存在的」。 本沙箱的外键默认是关的，要 PRAGMA foreign_keys = ON； 而且它在事务里开是无效的，必须先 commit()。 MySQL 的 InnoDB 默认就是开的。 打开之后两端都拦：插不存在的父值、删还有子行指着的父行。 删父行时子行怎么办，由 ON DELETE CASCADE／SET NULL 决定，不写就是拒绝。

📝 本章和前面几章的关系

前七篇的约束都只管一张表内部，这一篇是唯一一个跨表的属性。 它是「关系型数据库」里「关系」两个字最直接的体现： 两张表靠它连起来，数据的一致性靠它守住。 但它也是性能与灵活性的一个取舍点，最后一节会说。

一、建表：声明「我指向谁」

先建父表、再建子表，子表里写：FOREIGN KEY (class_id) REFERENCES p_class (id)。 读法：「我的 class_id 必须是 p_class 里存在的 id」。 外键写在表级，因为它牵涉两列（本表的列和父表的列）， 没法挂在某一列后面。 也可以写成列级 class_id INTEGER REFERENCES p_class (id)，但表级写法能一次声明多列外键，也更容易加 ON DELETE 子句。 父表被引用的那一列必须有索引—— 通常是主键或唯一键。 MySQL 强制要求这一点，SQLite 不检查但会慢。

二、先要打开开关，而且不能在事务里开

PRAGMA foreign_keys 默认给 0，关着的时候外键完全不生效，插一个不存在的班级也不报错。 为什么默认关：老版本的 SQLite 没有外键， 为了不破坏老代码的兼容性，默认保持「不检查」。 MySQL 的 InnoDB 默认是开的，这一处差别会让人以为「外键没用了」。 还有一个坑：PRAGMA foreign_keys 在事务里执行是无效的，而且不报错。 Python 的 sqlite3 默认在第一次写操作时开事务， 所以 Demo 里执行完 PRAGMA foreign_keys = ON，回头再查还是 0。 要先 commit() 才能开。 这一条「开了但没生效、还不报错」和 9.4 篇那个 AUTO_INCREMENT 是同一类问题： 静默失败最耗时间，因为你会一直以为自己已经设对了。

三、打开之后：孤儿插不进去，父行删不掉

插一个 class_id = 99 的学生，报 FOREIGN KEY constraint failed。 删一个还有学生指着的班，也报同一个错。 MySQL 那边的报错更具体：插入孤儿时是 Cannot add or update a child row，删父行时是 Cannot delete or update a parent row。 一个说「子行不对」，一个说「父行不能动」，方向正好相反。 这两条拦的都是「引用完整性」： 不允许有指向不存在的东西的引用。 代价是删除变麻烦了——得先想清楚子行怎么办。

四、级联：删父行时子行怎么办

ON DELETE CASCADE：父行一删，子行跟着删（Demo 里两个学生一起没了）。 ON DELETE SET NULL：子行留着，把外键列置空。 默认是 RESTRICT——什么都不做，直接拒绝。 用哪种要按业务定，但一定要显式写出来： 不写就是 RESTRICT，很多人以为是 CASCADE，一删父表才发现删不动。 CASCADE 要特别小心：它是一条「连锁删除」的授权。 删一个班级，四个学生的成绩、订单、日志可能跟着一路删下去。 真 MySQL 上，一次误删父行可能带走几十万行子数据，而且没有确认提示。 所以生产环境里 CASCADE 常常被禁用，改用「先删子、再删父」的显式流程。 还有 ON UPDATE，管的是父键被改时子行怎么办。 主键一般不改，所以这个子句用得少，但真要用「业务编号当主键」的系统里它很关键。

五、目标表不存在也能把表建出来

REFERENCES nosuch (id) 建表成功，插入时报 no such table: main.nosuch。 SQLite 对外键的目标表是「用到时才查」，建表那一刻不校验。 MySQL 相反：目标表不存在，建表就直接失败。 所以「先建父表、再建子表」在 MySQL 上是硬要求。 这个差别在「导入建表脚本」时会影响顺序： 脚本里表的先后顺序不对，MySQL 会直接报错，SQLite 一声不响。 所以建表脚本最好按「父表在前」排，两边都能跑。

六、外键的实际代价

每次写子表都要去父表查一次，多一次索引查找； 每次删父表都要去子表查一次有没有人指着。 数据量大时这两笔开销是实打实的。 所以有些团队选择「不用数据库外键，靠程序保证」—— 换来的是灵活性（分库分表时外键根本没法建），代价是脏数据迟早会出现。 这是个需要明确决策的取舍，不是「哪个更先进」的问题。 有外键，一致性由数据库兜底；没外键，一致性全靠代码纪律。 选后者就要有对应的巡检脚本，定期查「有没有指向不存在父行的子行」。

七、动手 Demo：先撞一次「开关关着」，再打开看两端都拦

Demo 五段：建父子两张表，先看开关是 0；在事务里开一次（无效）、commit 后再开一次（生效）；然后撞孤儿插入和删父行；再做一次级联删除；最后建一张指向不存在表的外键。 第二段那个「开了还是 0」是这一篇最该记住的细节。

import _q46 as Q
c = Q.blank()
Q.show(c, "PRAGMA foreign_keys")        # 0，默认关

Q.run(c, "CREATE TABLE p_class (id INTEGER PRIMARY KEY, name TEXT)")
Q.run(c, "CREATE TABLE p_stu (id INTEGER PRIMARY KEY, name TEXT, "
          "class_id INTEGER, "
          "FOREIGN KEY (class_id) REFERENCES p_class (id))")

Q.run(c, "PRAGMA foreign_keys = ON")    # 事务里开，无效
c.commit()
Q.run(c, "PRAGMA foreign_keys = ON")    # 这次才生效

c.execute("INSERT INTO p_stu VALUES (1, '张伟', 99)")   # 孤儿
c.execute("DELETE FROM p_class WHERE id = 1")          # 父行

# 级联：删父行时子行跟着删
Q.run(c, "CREATE TABLE c_class (id INTEGER PRIMARY KEY, name TEXT)")
Q.run(c, "CREATE TABLE c_stu (id INTEGER PRIMARY KEY, name TEXT, "
          "class_id INTEGER, "
          "FOREIGN KEY (class_id) REFERENCES c_class (id) "
          "ON DELETE CASCADE)")
Q.run(c, "DELETE FROM c_class WHERE id = 1")

实跑输出：

--------------------------------------------------------------------
第四十九课：外键——连着两张表的那个属性
--------------------------------------------------------------------

  建表：声明「我指向谁」
  **先建父表、再建子表**，顺序反了在真 MySQL 上会报错（不过这里宽松，先建子表也能过）。
  SQL> CREATE TABLE p_class (id INTEGER PRIMARY KEY, name TEXT)
  完成
  SQL> CREATE TABLE p_stu (id INTEGER PRIMARY KEY, name TEXT, class_id INTEGER,
       FOREIGN KEY (class_id) REFERENCES p_class (id))
  完成
  SQL> INSERT INTO p_class VALUES (1, '一班')
  影响 1 行
  SQL> INSERT INTO p_stu VALUES (1, '张伟', 1)
  影响 1 行
  SQL> SELECT id, name, class_id FROM p_stu
  id     name     class_id
  -------------------------
  1      张伟     1
  （1 行）
  **`FOREIGN KEY (class_id) REFERENCES p_class (id)`**——
  **「我的 `class_id` 必须是 `p_class` 里存在的 `id`」。**

  先要打开开关，而且不能在事务里开
  SQL> PRAGMA foreign_keys
  foreign_keys
  -------------
  0
  （1 行）
  **是 0：这个开关默认是关的。** **关着的时候外键完全不生效**，插一个不存在的班级也不报错。
  **为什么默认关**：老版本的 SQLite 没有外键，
  **为了不破坏老代码的兼容性，默认保持「不检查」。** **MySQL 的 InnoDB 默认是开的**，
  这一处差别会让人以为「外键没用了」。
  **还有一个坑：`PRAGMA foreign_keys` 在事务里执行是无效的，而且不报错。**
  **Python 的 sqlite3 默认在第一次写操作时开事务**，
  **所以这里执行完 `PRAGMA foreign_keys = ON`，回头再查还是 0。**
  SQL> PRAGMA foreign_keys = ON
  完成
  SQL> PRAGMA foreign_keys
  foreign_keys
  -------------
  0
  （1 行）
  **要打开它，得先 `commit()` 把事务结束掉。**
  **下面对着一张新连接演示：先 `commit()`，再开开关。**

  打开之后：孤儿插不进去，父行删不掉
  SQL> PRAGMA foreign_keys
  foreign_keys
  -------------
  1
  （1 行）
  SQL> INSERT INTO p_class VALUES (1, '一班')
  影响 1 行
  SQL> INSERT INTO p_stu VALUES (1, '张伟', 99)
  报错：FOREIGN KEY constraint failed
  **两条都被拦**：一条是「插一个不存在的班」，
  **一条是「删掉还有学生指着的班」。** **报错都是 `FOREIGN KEY constraint failed`。**
  **MySQL 那边的报错更具体**：插入孤儿时是 `Cannot add or update a child row`，
  删父行时是 `Cannot delete or update a parent row`。
  **一个说「子行不对」，一个说「父行不能动」，方向正好相反。**

  级联：删父行时子行怎么办
  SQL> SELECT COUNT(*) AS before_del FROM c_stu
  before_del
  -----------
  2
  （1 行）
  SQL> DELETE FROM c_class WHERE id = 1
  影响 1 行
  SQL> SELECT COUNT(*) AS after_del FROM c_stu
  after_del
  ----------
  0
  （1 行）
  **`ON DELETE CASCADE`**：父行一删，子行跟着删。 **两个学生一起没了。**
  **另一种是 `ON DELETE SET NULL`**：子行留着，把外键列置空。
  **适合「分类删了，但商品还得留着」这种场景。** **默认是 `RESTRICT`**——什么都不做，直接拒绝。
  **用哪种要按业务定，但一定要显式写出来**：
  **不写就是 RESTRICT，很多人以为是 CASCADE，一删父表才发现删不动。**

  目标表不存在也能把表建出来
  SQL> CREATE TABLE c_orphan (id INTEGER PRIMARY KEY, pid INTEGER,
       FOREIGN KEY (pid) REFERENCES nosuch (id))
  完成
  SQL> INSERT INTO c_orphan VALUES (1, 1)
  报错：no such table: main.nosuch
  **建表成功，插入时报 `no such table: main.nosuch`。**
  **SQLite 对外键的目标表是「用到时才查」，建表那一刻不校验。**
  **MySQL 相反**：目标表不存在，建表就直接失败。
  **所以「先建父表、再建子表」在 MySQL 上是硬要求。**

  外键的实际代价
  **每次写子表都要去父表查一次**，多一次索引查找；
  **每次删父表都要去子表查一次有没有人指着。** **数据量大时这两笔开销是实打实的。**
  **所以有些团队选择「不用数据库外键，靠程序保证」**——
  **换来的是灵活性（分库分表时外键根本没法建），代价是脏数据迟早会出现。**
  **这是个需要明确决策的取舍，不是「哪个更先进」的问题。**

第一段开关是 0，外键默认不生效。 第二段在事务里开，回头查还是 0——PRAGMA 在事务里改不了，而且不报错。 第三段新连接先 commit 再开，查到 1，生效了。 第四段两条都被拦：孤儿插入报 FOREIGN KEY constraint failed，删父行同一个报错。 第五段级联：删掉班级，两个学生一起没了（before_del 是 2、after_del 是 0）。 第六段建了一张指向 nosuch 的表，建表成功、插入才报错。

30 秒实验

本沙箱里 PRAGMA foreign_keys = ON 之后又查还是 0，最可能的原因是什么？
ON DELETE 不写时默认是哪种行为？它和 CASCADE 差在哪？
为什么有些团队宁可不用数据库外键？代价是什么？

常见误区

以为建了外键就生效。 这个沙箱默认是关的，而且开关在事务里开无效，要先 commit。
随手用 ON DELETE CASCADE。 它是一条连锁删除授权，删一个父行可能带走几十万行子数据。
外键和程序校验二选一。 有外键也别省程序校验（报错更友好），没外键就必须有巡检脚本，否则脏数据迟早出现。

自测三问（点开对答案）

1. 本沙箱的外键默认生效吗？怎么打开？ —— 默认不生效，`PRAGMA foreign_keys` 是 0。要 `commit()` 之后再执行 `PRAGMA foreign_keys = ON`，因为它在事务里开无效。 2. 打开外键之后，哪两种操作会被拦？ —— 插入一个父表里不存在的值（孤儿），和删除还有子行指着的父行。 3. `ON DELETE CASCADE` 和 `SET NULL` 分别做什么？默认是哪个？ —— `CASCADE` 连子行一起删，`SET NULL` 把子行的外键列置空，默认是 `RESTRICT`（直接拒绝）。

约束讲完了，剩一个最不拦数据的属性。 下一篇 9.9 注释——写给人看的那一个。