一、五类完整性，各管一段

这张表最该记住的是最后一列的分布：前三类是「声明式的」，写一句约束就永久生效； 后两类要你自己写代码去守，而代码是会漏的。 所以有一条设计原则：能在前三类解决的规则，别放到第五类。 「余额不能为负」用 CHECK 一行搞定，比写触发器可靠得多—— 触发器可以被禁用、可以被绕过，CHECK 不会。

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

建一张带主键和唯一列的表，然后两次撞上去：

两条报错长得几乎一样，因为底层是同一套机制——主键在存储上就是一个唯一索引。 差别只有一条：主键不许 NULL，唯一索引允许（而且允许多个 NULL）。 所以「必填且唯一」用主键，「可以不填但填了就不能重」用唯一索引。 实体完整性的意义是「每行都能被准确指出来」。 没有主键的表， 你没法说「改第三行」——因为「第三行」是随机的，下次查可能就换位置了。 这也是为什么「每张表都该有主键」不是教条，是操作需要。

三、域完整性：CHECK、默认值、非空

一张表上三条约束，三条边界各撞一次：

三条都没进去，但拦它们的是不同的东西。 前两条是 CHECK， 第三条是 NOT NULL——它不是 CHECK，是单独的约束，报错信息也不一样。 默认值的行为要特别注意：只在「不写这一列」时生效。 写 NULL 进去还是 NULL，不会变成默认值。演示里第四条故意不写 score， 取回来是 0——这才是默认值生效的样子。 CHECK 有一条容易踩的坑：CHECK 通过（返回真或「未知」）就放行。 所以 CHECK (age > 0) 对 NULL 是不拦的——NULL > 0 的结果是「未知」，不是假。 要连 NULL 一起拦，得再加 NOT NULL。

四、引用完整性：外键，以及那个默认关闭的开关

建子表、指向父表，然后插一个不存在的父：

双向都拦住了，这就是引用完整性的两层意思： 子不能指向不存在的父，父也不能丢下还在用它的子。 但这里有个 SQLite 特有的坑，实测第一遍就撞上了：外键约束默认是关的。 每个连接都要执行一次 PRAGMA foreign_keys = ON， 否则外键写了等于没写——插不存在的父照样成功，删被引用的父也照样成功，而且不报任何错。 从 SQL Server 迁过来的人最容易栽在这上面，因为 SQL Server 建了约束就生效， 没有这个开关。 所以在这套环境里， 「外键没报错」不代表「外键在管」——先查 PRAGMA foreign_keys 是不是 1。 另一头是级联： SQL Server 可以写 ON DELETE CASCADE，让父被删时自动删子。 很方便，也很危险——一次误删能顺着级联删掉半张库，而且不问你第二遍。 默认的「拦住不让删」虽然烦，但它把决定权留给了人。

五、事务完整性：留给第 15 章

前面四类都是「单条语句」级别的约束：插一行、改一行、删一行，各管各的。 事务完整性管的是「一批语句」：转账要扣款和入账一起成、一起败，中间断了就全回滚。 它跟前面四类的关键区别是：它管的是「过程」，不是「值」。 CHECK 管不了「扣了款没入账」这种状态——因为每一步单独看都合法，错在「只做了一半」。 第 15 章会讲四个特性（原子性、一致性、隔离性、持久性）、显式与隐式事务、四种隔离级别、 以及分布式事务。 这一节只要记住：它补的是前面四类都补不上的那个洞。

六、动手 Demo：五道关卡，一条一条撞上去

这一段把主键、唯一、CHECK、NOT NULL、外键逐个撞一遍，看每种违反时报什么错； 最后确认两件事：默认值什么时候生效、外键开关打不开差别有多大。 注意 ④ 开头那句 PRAGMA foreign_keys = ON——不加它，下面两条外键测试全是假的。

import sqlite3

c = sqlite3.connect(u":memory:")
c.execute(u"CREATE TABLE tb_test_pk (id INTEGER PRIMARY KEY,"
          u" code TEXT UNIQUE, name TEXT)")
c.execute(u"INSERT INTO tb_test_pk (id, code, name)"
          u" VALUES (1, 'A001', '第一个')")
c.execute(u"INSERT INTO tb_test_pk (id, code, name) VALUES (1, 'A002', 'x')")
# -> UNIQUE constraint failed: tb_test_pk.id

c.execute(u"CREATE TABLE tb_test_domain (id INTEGER PRIMARY KEY,"
          u" age INTEGER CHECK (age BETWEEN 0 AND 150),"
          u" sex TEXT CHECK (sex IN ('男', '女')),"
          u" score REAL NOT NULL DEFAULT 0)")
c.execute(u"INSERT INTO tb_test_domain (id, age, sex, score)"
          u" VALUES (1, 200, '男', 60)")   # -> CHECK 拦住
c.execute(u"INSERT INTO tb_test_domain (id, age, sex) VALUES (4, 30, '女')")

c.execute(u"PRAGMA foreign_keys = ON")     # 不加这句，下面全是摆设
c.execute(u"CREATE TABLE tb_test_child (id INTEGER PRIMARY KEY,"
          u" pid INTEGER NOT NULL REFERENCES tb_test_pk(id), memo TEXT)")
c.execute(u"INSERT INTO tb_test_child (id, pid, memo) VALUES (1, 999, 'x')")
# -> FOREIGN KEY constraint failed

实跑输出：

--------------------------------------------------------------------
数据完整性：五道关卡
--------------------------------------------------------------------


  ① 五类完整性
  --------------------------------------------------------------------
| 类型 | 保什么 | 靠什么实现 |
|---|---|---|
| 实体完整性 | 每行都能被唯一认出 | 主键、唯一索引 |
| 域完整性 | 每列的值在合理范围内 | 类型、CHECK、默认值、非空 |
| 引用完整性 | 外键指向的值必须存在 | 外键 |
| 事务完整性 | 一批操作要么全成要么全败 | 事务（第 15 章） |
| 用户定义完整性 | 业务规则 | 触发器、存储过程、应用层 |

  前三类是数据库能替你保证的，第四类要靠事务，第五类只能自己写——
  分清楚这个界线很重要：能在前三类解决的，别写到第五类去（约束比触发器可靠得多）。

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

  1> CREATE TABLE tb_test_pk (id INTEGER PRIMARY KEY, code TEXT UNIQUE, name TEXT)

  1> INSERT INTO tb_test_pk (id, code, name) VALUES (1, 'A001', '第一个')
  插重复主键：

  1> INSERT INTO tb_test_pk (id, code, name) VALUES (1, 'A002', '重复主键')
  消息 102，级别 15，状态 1
  UNIQUE constraint failed: tb_test_pk.id
  插重复唯一键：

  1> INSERT INTO tb_test_pk (id, code, name) VALUES (2, 'A001', '重复编码')
  消息 102，级别 15，状态 1
  UNIQUE constraint failed: tb_test_pk.code
  两种都挡住了。主键和唯一索引的差别只有一个：主键不许 NULL，唯一索引允许（而且允许多个 NULL）。

  ③ 域完整性：类型、CHECK、默认值、非空
  --------------------------------------------------------------------

  1> CREATE TABLE tb_test_domain ( id INTEGER PRIMARY KEY, age INTEGER CHECK (age
         BETWEEN 0 AND 150), sex TEXT CHECK (sex IN ('男', '女')), score REAL NOT NULL
         DEFAULT 0)
  三条边界各试一次：年龄 200、性别「未知」、分数留空。

  1> INSERT INTO tb_test_domain (id, age, sex, score) VALUES (1, 200, '男', 60)
  消息 102，级别 15，状态 1
  CHECK constraint failed: age BETWEEN 0 AND 150

  1> INSERT INTO tb_test_domain (id, age, sex, score) VALUES (2, 30, '未知', 60)
  消息 102，级别 15，状态 1
  CHECK constraint failed: sex IN ('男', '女')

  1> INSERT INTO tb_test_domain (id, age, sex, score) VALUES (3, 30, '男', NULL)
  消息 102，级别 15，状态 1
  NOT NULL constraint failed: tb_test_domain.score
  三条都没进去。最后一条是 NOT NULL 挡的——注意它不是 CHECK，是单独的约束。

  1> INSERT INTO tb_test_domain (id, age, sex) VALUES (4, 30, '女')

  1> SELECT id, age, sex, score FROM tb_test_domain
  id  age  sex  score
  -------------------
  4   30   女   0
  (1 行受影响)
  这一条成功了，score 用了默认值 0。默认值只在「不写这一列」时生效，写 NULL 还是 NULL。

  ④ 引用完整性：外键
  --------------------------------------------------------------------

  1> PRAGMA foreign_keys = ON
  这一步 SQL Server 上不需要——外键建好就生效。SQLite 默认是关的，不打开这个开关，外键写了也白写，
  这是从 SQL Server 迁过来最容易踩的坑。确认一下开关状态：
  PRAGMA foreign_keys 现在返回 1。


  1> CREATE TABLE tb_test_child ( id INTEGER PRIMARY KEY, pid INTEGER NOT NULL
         REFERENCES tb_test_pk(id), memo TEXT)
  插一个不存在的 pid：

  1> INSERT INTO tb_test_child (id, pid, memo) VALUES (1, 999, '父不存在')
  消息 102，级别 15，状态 1
  FOREIGN KEY constraint failed

  1> INSERT INTO tb_test_child (id, pid, memo) VALUES (2, 1, '父存在')

  1> SELECT id, pid, memo FROM tb_test_child
  id  pid  memo
  ---------------
  2   1    父存在
  (1 行受影响)
  父不存在的那条被拦住了，父存在的进去了。
  再试一件事：删掉被引用的父行。

  1> DELETE FROM tb_test_pk WHERE id = 1
  消息 102，级别 15，状态 1
  FOREIGN KEY constraint failed
  也被拦住了——这就是引用完整性的双向保护：子不能指向不存在的父，父也不能丢下还在用它的子。
  SQL Server 里可以用 ON DELETE CASCADE 让父被删时自动删子，但那要非常小心——
  一次误删能顺着级联删掉半张库。 默认的「拦住不让删」更安全。

  ⑤ 完整性检查清单
  --------------------------------------------------------------------
| 检查项 | 怎么查 |
|---|---|
| 每张表都有主键吗 | 看表定义；没主键的表没法准确定位某一行 |
| 关键列有没有 NOT NULL | 允许 NULL 的列，聚合和比较都会出意外 |
| 外键都有约束吗 | 只有「逻辑外键」没有约束的表，早晚出现孤儿行 |
| 状态列有没有 CHECK | 不然会出现「已发货」「已发贷」两种写法混在一起 |
| 日期列有没有范围检查 | 出现过 1900-01-01 和 2999-12-31 的表都该补 |

  这份清单建议对每个新库都过一遍。这些约束是「一次写对、永久生效」的投资——
  比事后写脚本清洗数据便宜得多。

报错信息就是线索，不用背。 UNIQUE constraint failed: 表.列 是主键/唯一撞了， CHECK constraint failed: 条件 是 CHECK 没通过， NOT NULL constraint failed: 表.列 是漏了必填，FOREIGN KEY constraint failed 是外键。 CHECK 报错里会原样打出你写的条件——age BETWEEN 0 AND 150、sex IN ('男', '女')。 所以写 CHECK 时把条件写清楚，报错就是一句现成的提示。 默认值那一条的关键是「不写这一列」：VALUES (4, 30, '女') 没有 score， 取回来的 score 是 0。如果写成 VALUES (4, 30, '女', NULL)，那就是 NULL，不是 0。 外键那两条测试一定要先开开关。 这一段的代码里 PRAGMA 在 CREATE TABLE 之前， 顺序也讲究：连接一建立就开，别等到插入前才想起来——中间那条语句出错就会被漏掉。

30 秒实验

1. 把 PRAGMA foreign_keys = ON 注释掉，重跑。 插 pid = 999 会成功，删父行也会成功——而且一句提示都没有。 这就是「外键写了等于没写」的样子，最坑的地方是它不报错。
2. 给 tb_test_domain 加一条 CHECK (score >= 0)，然后插 score = -1 试试。 会被拦——说明 CHECK 能写在已有的列上、也能引用多列（行级检查）。
3. 想一想：如果业务要求「余额不能为负」，你会用 CHECK、触发器还是应用层代码判断？ 首选 CHECK（一条语句、绕不过）；涉及多表汇总的才退到触发器； 应用层判断只能当补充，不能当唯一防线。

常见误区

1. 「外键建了就在生效」。 SQLite 上默认是关的，不开 PRAGMA foreign_keys 就等于没建。 而且它不报错——数据悄悄变脏，等你发现时已经几千条孤儿行。
2. 「CHECK 能拦住 NULL」。CHECK 的结果是「未知」时是放行的， NULL > 0 就是「未知」。要拦 NULL 必须单独加 NOT NULL。
3. 「有默认值就不会有 NULL」。默认值只在「不写这一列」时生效。 显式写 NULL 进去，存的就是 NULL。 想彻底杜绝，得再加 NOT NULL。

下一篇 14.5 讲维护： 怎么查看索引、怎么判断索引被没被用上、重建与统计信息的耗时，以及一份按周按月排的维护清单。

• ☐ （本篇完，下一篇 14.5 维护与管理）

相关笔记

• 维护与管理
• 索引的创建
• 管理数据表
• 15 课 数据库基础
• 🪟 SQL Server从入门到精通 · 索引