一、三类完整性，四种约束

教材按「管什么」把完整性分成三类，落到建表语句里就是四种约束。

「实体完整性」这个说法听着抽象，其实就一句话：这一行得能被唯一认出来。 主键干这个，唯一约束是它的补充——一张表只能有一个主键，但可以有多个唯一约束。 第三类「用户定义」就是业务规则：价格不能为负、状态只能是几个固定值。 这类规则原本写在应用代码里，写进 CHECK 之后就由数据库兜底了。

二、实体完整性：主键与唯一

插一条重号的主键，报的是 UNIQUE constraint failed: tb_cat.id。 但插一条主键为 NULL 的记录，居然成功了。

原因在 INTEGER PRIMARY KEY 身上：它就是 rowid，而 rowid 遇到 NULL 会「替你编一个」。 所以「主键不许为空」这句话要打个折——它不许的是重复。 想真的不许空，得在列上加 NOT NULL，光写主键不够。 这是一处很小的分叉，但真会踩：以为主键就够，结果插进了一堆空值。

三、非空：NOT NULL

name 上有 NOT NULL，插空值报 NOT NULL constraint failed: tb_goods.name。 这句报错把表名和列名都写全了，比主键那句好用。 报错质量不一样，这件事在排错时很关键。 外键那句只有 FOREIGN KEY constraint failed，什么信息都没有——下一篇会专门说。

四、参照完整性：外键默认不管事

这是这一章最该记住的一条：外键写在建表语句里，但默认不生效。

99 这个类别根本不存在，但它插进去了。 换一个连接进来，这个开关又回到关的状态——它是连接级的设置，不是库级的。 打开之后两种操作都被拦：插一条指向不存在父行的记录、删一条还被引用的父行。 第二种更值得注意：约束管的不只是新数据，也管你删旧数据。

五、用户定义完整性：CHECK 与 DEFAULT

CHECK 拦非法值，DEFAULT 补缺失值，两件事管的方向相反。

插 price = -5 被拦，报 CHECK constraint failed: ck_price。 不写 price 那一列没报错，它被补成了 0。 两个都写，才叫「这一列的值既合法又完整」。 只写 CHECK，空值不会被拦（NULL >= 0 是「不知道」，不是「假」）。 这一点容易忽略：CHECK 对 NULL 是放行的。 想禁止空值，得另外加 NOT NULL，两个约束各管一件事。

六、约束挡住之后，脏数据没进表

再插一条负价的记录，报错照旧，表里的行数一点没变。 约束是在写入那一瞬间拦住的，不存在「先写进去再回滚」。 所以它是最后一道防线：应用层校验可以跳过（换个客户端、跑个脚本），约束跳不过。 但这不代表应用层可以不校验——约束的报错对用户不友好，只能兜底。

七、外键的四个动作

外键后面能跟一个动作，决定「删父行时子表怎么办」。默认是 NO ACTION。

实测 CASCADE：父行删掉之后，两条子行跟着消失，子表从 3 行变 1 行。 NO ACTION 就是上一节看到的「直接拒绝」。 选哪个动作要按业务定：订单明细该 CASCADE，账目记录应该 NO ACTION。 选错了不会报错，是数据悄悄少了一批——这种事最难查。

八、动手 Demo：四种约束各撞一次墙

这一段建两张表，然后依次让主键、非空、外键、CHECK 各失败一次，把报错原文原样记下来。 第三节专门演示「外键默认关着」这件事。

import sqlite3
c = sqlite3.connect(u":memory:", isolation_level=None)
c.executescript(u"""
CREATE TABLE tb_cat (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE tb_goods (
  id INTEGER PRIMARY KEY, name TEXT NOT NULL, cat_id INTEGER,
  price REAL DEFAULT 0,
  CONSTRAINT ck_price CHECK (price >= 0),
  CONSTRAINT fk_cat FOREIGN KEY (cat_id) REFERENCES tb_cat (id));
INSERT INTO tb_cat VALUES (1, '五金'), (2, '劳保');
INSERT INTO tb_goods VALUES (1, '螺丝刀', 1, 25.0), (2, '手套', 2, 8.0);
""")

# ① 实体完整性
def try_(fn):
    try:
        fn()
        return u"成功"
    except sqlite3.Error as e:
        return u"%s" % e

print(try_(lambda: c.execute(u"INSERT INTO tb_cat VALUES (1, '重号')")))
print(try_(lambda: c.execute(u"INSERT INTO tb_cat VALUES (NULL, '空号')")))
print(c.execute(u"SELECT * FROM tb_cat").fetchall())

# ② 非空
print(try_(lambda: c.execute(u"INSERT INTO tb_goods (id, name) "
                             u"VALUES (9, NULL)")))

# ③ 外键：先看开关，再看打开后的行为
print(c.execute(u"PRAGMA foreign_keys").fetchone()[0])
print(try_(lambda: c.execute(u"INSERT INTO tb_goods VALUES (3, '野', 99, 10)")))
c.execute(u"PRAGMA foreign_keys = ON")
print(try_(lambda: c.execute(u"INSERT INTO tb_goods VALUES (4, '野2', 99, 10)")))
print(try_(lambda: c.execute(u"DELETE FROM tb_cat WHERE id = 1")))

# ④ CHECK 与 DEFAULT
print(try_(lambda: c.execute(u"INSERT INTO tb_goods VALUES (5, '负价', 1, -5)")))
print(try_(lambda: c.execute(u"INSERT INTO tb_goods (id, name, cat_id) "
                             u"VALUES (6, '没写价', 1)")))
print(c.execute(u"SELECT price FROM tb_goods WHERE id = 6").fetchone())

实跑输出：

--------------------------------------------------------------------
第五十七课：三种完整性，四种约束，一个默认关着的开关
--------------------------------------------------------------------

  **教材 13.1 讲三类完整性：实体、参照、用户定义。**
  **这一课把它们落到四种具体约束上，每一种都撞一次墙。**
  **撞墙比看定义有用：报错原文里写着约束的名字和列名，那才是能用来查错的东西。**

  一、实体完整性：主键与唯一
  --------------------------------------------------------------------

  动作         结果
  ------------------------------------------------
  主键重复     UNIQUE constraint failed: tb_cat.id
  主键为 NULL  成功
  **第一行报 `UNIQUE constraint failed: tb_cat.id`，第二行却成功了。**
  **因为 `INTEGER PRIMARY KEY` 就是 rowid，把 NULL 当成「你替我编一个」，于是给了 3。**

  mysql> SELECT * FROM tb_cat
  id name
  -------
  1  五金
  2  劳保
  3  空号
  3 rows in set
  **看结果那第三行：它拿到的是 3，不是空。**
  **所以「主键不许为空」这句话在沙箱里要打个折——它不许的是「重复」。**
  **想真的不许空，得给列上加 `NOT NULL`，光写主键不够。**

  二、非空：NOT NULL
  --------------------------------------------------------------------
  **`name` 上有 `NOT NULL`，插空值报的是 `NOT NULL constraint failed: tb_goods.name`。**
  **这句报错把「哪张表哪一列」都写全了，比主键那句更好用。**
  **报错质量不一样，这件事在排错时很关键。**

  三、参照完整性：外键默认不管事
  --------------------------------------------------------------------
  **先看开关：`PRAGMA foreign_keys` 给 0，也就是关着。**

  开关        插父表里没有的 cat_id  表里行数
  -------------------------------------------
  关（默认）  成功                   2 → 3
  **`99` 这个类别根本不存在，但它插进去了，行数从 2 变 3。**
  **外键写在建表语句里，默认却是不生效的——这一条最容易被忽略。**

  开关  动作                 结果
  --------------------------------------------------------
  开    插父表没有的 cat_id  FOREIGN KEY constraint failed
  开    删还被引用的父行     FOREIGN KEY constraint failed
  **打开之后两边都被拦住了，报的是同一句 `FOREIGN KEY constraint failed`。**
  **注意这句报错没说是哪张表、哪一列——这就是外键报错的短板，后面一篇还会提。**

  四、用户定义完整性：CHECK 与 DEFAULT
  --------------------------------------------------------------------

  动作          结果
  -----------------------------------------------
  `price = -5`  CHECK constraint failed: ck_price
  不写 `price`  成功

  mysql> SELECT id, name, price FROM tb_goods WHERE id = 6
  id name   price
  ---------------
  6  没写价 0
  1 row in set
  **第一行被 `CHECK (price >= 0)` 拦了，报错里带着条件表达式。**
  **第二行没报错，`DEFAULT 0` 把价格补成了 0。**
  **`CHECK` 和 `DEFAULT` 是两件事：前者拦非法值，后者补缺失值。**
  **两个都写，才叫「这一列的值既合法又完整」。**

  五、约束挡住之后，脏数据没进表
  --------------------------------------------------------------------

  时机        行数
  ----------------
  插负价之前  4
  插负价之后  4
  **报错是 `CHECK constraint failed: ck_price`，行数一点没变。**
  **约束是在写入的那一瞬间拦住的，不存在「先写进去再回滚」这个过程。**
  **所以它是最后一道防线，比应用层校验更可靠——应用能绕过，约束绕不过。**

  六、外键的三个动作
  --------------------------------------------------------------------

  动作                   子表行数
  -------------------------------
  删父行之前             3
  删父行之后（CASCADE）  1

  mysql> SELECT * FROM tb_c
  id pid
  ------
  3  2
  1 row in set

  动作                 删父行时子表怎么办
  -------------------------------------------
  `NO ACTION`（默认）  拒绝删，报外键错误
  `CASCADE`            子表跟着删掉
  `SET NULL`           子表外键列置 NULL
  `RESTRICT`           同 NO ACTION，立刻检查
  **这次是 `CASCADE`：父行删掉，两条子行跟着没了，只剩 `pid = 2` 那条。**
  **默认的 `NO ACTION` 就是上一节看到的那种：直接拒绝。**
  **选哪个动作要按业务定：订单明细该 `CASCADE`，账目记录应该 `NO ACTION`。**
  **选错了不是报错，是数据悄悄少了一批——这种事最难查。**

第一组两个结果很值得对照：第一行报 UNIQUE，第二行给「成功」。 紧接着打出的表里能看到那一行拿到了 3——NULL 被理解成「自动编号」。 第三组三行是这一篇的核心：开关读到 0，接着插一条野数据成功了； 打开开关之后，同样的插入被拦，删被引用的父行也被拦。 第四组三行：负价被 CHECK 拦，不写价格那一行成功且被补成 0。 CHECK 管合法，DEFAULT 管完整，这条分工在这里看得很清楚。 最后一段是 CASCADE 的实测：父行删掉，两条子行跟着没了，子表只剩 1 行。 换成默认的 NO ACTION，这一步会直接报错。

30 秒实验

1. 把 id INTEGER PRIMARY KEY 改成 id INTEGER PRIMARY KEY NOT NULL，再插一次空值。
2. 把 ck_price 的条件改成 price > 0，试插 price = 0，看拦不拦。
3. 把外键的 ON DELETE CASCADE 去掉，重跑删父行那一步，看报什么错。

常见误区

1. 以为写了外键就有人在管，其实沙箱默认不检查，脏数据一路插到底。
2. 以为主键就一定不许为空，INTEGER PRIMARY KEY 遇到 NULL 会替你编号。
3. 把 CHECK (price >= 0) 当成「不许为空」，空值其实是放行的，得另加 NOT NULL。

约束定义会了，下一篇讲怎么给它们起名字——不起名，将来就删不掉。

• ☐ （本篇完，下一篇 13.2 命名完整性约束）

相关笔记

• 创建数据表
• 重命名表
• 添加数据
• 🛢️ MySQL从入门到精通 · 索引