第七单元 · 约束与外键 · 第 15 / 21 节

约束：让数据库替你挡住坏数据

建一张 lesson15_constraint，把主键自增、非空、唯一、默认、检查五种约束写在同一张表上，用 information_schema 读出列属性与约束名；故意插重复姓名、空姓名、越界年龄，逐个看 1062 / 1048 / 3819，并弄清 CHECK 为什么拦不住 NULL、AUTO_INCREMENT 为什么不回填空号。

2 个步骤 · 进度只存本机

约 22 分钟。这一节只建一张表，把五种约束写在列上，然后故意插三次坏数据，看数据库用什么错误码把它们挡回去。

SSH / Linux 终端MySQL 终端输出示例（只读）复制按钮只复制命令，不含 $ / mysql> 提示符和输出。

已完成 0 / 2 步

你现在在哪个提示符

应该在 mysql>。断了就先回到学习终端（PowerShell）：

SSH / Linux 终端bash

ssh root@8.148.204.152

SSH / Linux 终端bash

mysql-learning

进去以后确认当前库：

MySQL 终端sql

SELECT DATABASE();

输出示例（只读，不要粘贴执行）text不可复制

+------------+
| DATABASE() |
+------------+
| itcast     |
+------------+
1 row in set (0.00 sec)

显示 itcast 就对了。如果是 NULL，执行 USE itcast;——它只切换当前连接用哪个库，不动数据。

第一步：约束就是写在列上的规则

这一步做完了

“年龄不能是负数”“名字不能空”“编号不能重复”——这些规则如果只写在程序里，换个入口（手工改、导入脚本、另一个人写的接口）就漏了。约束是把规则写在表结构上，由数据库在每次写入时检查。

MySQL 里有六类：

约束	管什么	一句话
`NOT NULL`	不许空	这一列必须有值
`UNIQUE`	不许重复	可以有多个 NULL，但非 NULL 值不能重
`PRIMARY KEY`	唯一标识一行	非空 + 唯一，一张表只能有一个
`DEFAULT`	缺省值	插入时不写这一列，就用它
`CHECK`	取值范围	8.0.16 起真正生效，之前只解析不检查
`FOREIGN KEY`	表间引用	下一节单独讲

先建本节自己的表，把前五种一次写全：

MySQL 终端sql

SHOW TABLES LIKE 'lesson15_%';

输出示例（只读，不要粘贴执行）text不可复制

Empty set (0.00 sec)

Empty set 说明这个前缀下还没有表。已经有表就跳到本步末尾核对 4 行，不要删表重建。

MySQL 终端sql

CREATE TABLE lesson15_constraint (
  id    INT PRIMARY KEY AUTO_INCREMENT,
  name  VARCHAR(10) NOT NULL,
  age   INT,
  level CHAR(1) NOT NULL DEFAULT 'C',
  CONSTRAINT uk_lesson15_name UNIQUE (name),
  CONSTRAINT ck_lesson15_age CHECK (age > 0 AND age <= 120)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 0 rows affected (0.05 sec)

建完先读表结构，别急着插数据：

MySQL 终端sql

SELECT COLUMN_NAME, COLUMN_TYPE, IS_NULLABLE, COLUMN_DEFAULT, COLUMN_KEY, EXTRA
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = 'itcast' AND TABLE_NAME = 'lesson15_constraint'
ORDER BY ORDINAL_POSITION;

输出示例（只读，不要粘贴执行）text不可复制

+-------------+-------------+-------------+----------------+------------+----------------+
| COLUMN_NAME | COLUMN_TYPE | IS_NULLABLE | COLUMN_DEFAULT | COLUMN_KEY | EXTRA          |
+-------------+-------------+-------------+----------------+------------+----------------+
| id          | int         | NO          | NULL           | PRI        | auto_increment |
| name        | varchar(10) | NO          | NULL           | UNI        |                |
| age         | int         | YES         | NULL           |            |                |
| level       | char(1)     | NO          | C              |            |                |
+-------------+-------------+-------------+----------------+------------+----------------+
4 rows in set (0.00 sec)

一行一列读：

id：COLUMN_KEY 是 PRI（主键），EXTRA 是 auto_increment——不写它也会自动编号。
name：IS_NULLABLE 是 NO（非空），COLUMN_KEY 是 UNI（唯一索引）。
age：IS_NULLABLE 是 YES，允许空；范围交给 CHECK。
level：非空，COLUMN_DEFAULT 是 C。

再看约束自己的名字：

MySQL 终端sql

SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'itcast' AND TABLE_NAME = 'lesson15_constraint'
ORDER BY CONSTRAINT_TYPE, CONSTRAINT_NAME;

输出示例（只读，不要粘贴执行）text不可复制

+------------------+-----------------+
| CONSTRAINT_NAME  | CONSTRAINT_TYPE |
+------------------+-----------------+
| ck_lesson15_age  | CHECK           |
| PRIMARY          | PRIMARY KEY     |
| uk_lesson15_name | UNIQUE          |
+------------------+-----------------+
3 rows in set (0.00 sec)

三行：CHECK、PRIMARY KEY、UNIQUE。NOT NULL 和 DEFAULT 不在这张表里，它们是列属性（上面那一步的 IS_NULLABLE、COLUMN_DEFAULT 才是它们的位置）。给约束起名字（uk_lesson15_name、ck_lesson15_age）不是为了好看：以后要删或改某一条约束，得用名字指它，自动生成的名字很难猜。

插三条合法数据：

MySQL 终端sql

INSERT INTO lesson15_constraint (name, age) VALUES ('小甲', 19);

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 1 row affected (0.00 sec)

MySQL 终端sql

INSERT INTO lesson15_constraint (name, age) VALUES ('小乙', 25);

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 1 row affected (0.00 sec)

MySQL 终端sql

INSERT INTO lesson15_constraint (name, age, level) VALUES ('小丙', 31, 'B');

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 1 row affected (0.01 sec)

MySQL 终端sql

SELECT id, name, age, level FROM lesson15_constraint ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+----+--------+------+-------+
| id | name   | age  | level |
+----+--------+------+-------+
|  1 | 小甲   |   19 | C     |
|  2 | 小乙   |   25 | C     |
|  3 | 小丙   |   31 | B     |
+----+--------+------+-------+
3 rows in set (0.00 sec)

三行。前两条插入没写 level，它自动是 C——这就是 DEFAULT 在干活。第三条明确写了 B。id 是 1、2、3，因为 AUTO_INCREMENT 按顺序发号。

第二步：坏数据进不来，看错误码

这一步做完了

规则写在表上，接下来就故意违反它。一次只违反一条，这样每个错误码对应哪条约束才清楚。

先试重复的名字：

MySQL 终端sql

INSERT INTO lesson15_constraint (name, age) VALUES ('小甲', 40);

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1062 (23000): Duplicate entry '小甲' for key 'lesson15_constraint.uk_lesson15_name'

1062：name 上有 UNIQUE，小甲 已经存在。注意这条插入什么也没写进去，表还是 3 行。

再试空名字：

MySQL 终端sql

INSERT INTO lesson15_constraint (name, age) VALUES (NULL, 20);

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1048 (23000): Column 'name' cannot be null

1048：name 是 NOT NULL，NULL 不是“空字符串”，是“没有值”，直接被拒。

再试越界的年龄：

MySQL 终端sql

INSERT INTO lesson15_constraint (name, age) VALUES ('小丁', 130);

输出示例（只读，不要粘贴执行）text不可复制

ERROR 3819 (HY000): Check constraint 'ck_lesson15_age' is violated.

3819：CHECK (age > 0 AND age <= 120) 对 130 不成立。这条错误信息里带着约束名 ck_lesson15_age，所以给约束起名在这里就有回报了。

数一下，确认三次失败都没留下垃圾数据：

MySQL 终端sql

SELECT COUNT(*) AS 行数 FROM lesson15_constraint;

输出示例（只读，不要粘贴执行）text不可复制

+--------+
| 行数   |
+--------+
|      3 |
+--------+
1 row in set (0.00 sec)

还是 3 行。

**但 CHECK 不等于 NOT NULL。** 插入一条年龄留空的：

MySQL 终端sql

INSERT INTO lesson15_constraint (name, age) VALUES ('小戊', NULL);

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 1 row affected (0.01 sec)

成功了。因为 NULL > 0 的结果是“未知”（UNKNOWN），CHECK 只在结果为“假”时拒绝，未知按通过处理。所以“年龄必须有值”要用 NOT NULL，“年龄要在 1–120 之间”才用 CHECK，两件事别混。

最后看 AUTO_INCREMENT 的一个习惯：它不回填空号。先按名字删掉刚插入的小戊（不按 id 删，因为它的号已经不是你预期的那个），再加一个人：

MySQL 终端sql

DELETE FROM lesson15_constraint WHERE name = '小戊';

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 1 row affected (0.00 sec)

MySQL 终端sql

INSERT INTO lesson15_constraint (name, age, level) VALUES ('小己', 27, 'A');

输出示例（只读，不要粘贴执行）text不可复制

Query OK, 1 row affected (0.00 sec)

MySQL 终端sql

SELECT id, name, age, level FROM lesson15_constraint ORDER BY id;

输出示例（只读，不要粘贴执行）text不可复制

+----+--------+------+-------+
| id | name   | age  | level |
+----+--------+------+-------+
|  1 | 小甲   |   19 | C     |
|  2 | 小乙   |   25 | C     |
|  3 | 小丙   |   31 | B     |
|  6 | 小己   |   27 | A     |
+----+--------+------+-------+
4 rows in set (0.00 sec)

新的 小己 拿到 id = 6，不是 4，也不是 5。号去哪了？

4 被前面那次重复名字的插入吃掉了：唯一冲突是在取完号之后才发现的，号已经发出去，行却没写成；
5 是小戊，刚被删掉。

所以 AUTO_INCREMENT 只保证“不重复、递增”，不保证“连续”。这也是为什么编号不该被当成“第几个人”来解释。顺便记住另一件事：NULL 姓名（1048）和越界年龄（3819）那两次没有吃号——它们在取号之前就被拒绝了。

常见报错怎么办

看到	意思	怎么办
`1062 Duplicate entry`	唯一约束冲突	换一个值，或先 `SELECT` 查有没有同名
`1048 Column ... cannot be null`	非空约束	必须给这一列值；确实没有就改表结构，别塞空字符串糊过去
`3819 Check constraint ... is violated`	CHECK 表达式不成立	看错误里的约束名和表达式，核对取值范围
`1054 Unknown column`	列名写错	`SELECT ... FROM information_schema.COLUMNS` 或 `SHOW COLUMNS` 核对

说明

MySQL 终端sql

INSERT INTO lesson15_constraint (nmae, age) VALUES ('小庚', 20);

输出示例（只读，不要粘贴执行）text不可复制

ERROR 1054 (42S22): Unknown column 'nmae' in 'field list'

1054 是“找不到这个列”，不是“约束不让插”。先核对列名（name），再谈取值。

练习

先自己做，再展开提示和对答案。前两题动手（都用本节自己的表），后两题只问概念。

**1. 用一条查询说明：现在表里有几个人、其中 level 是 C 的有几个？**

说明

COUNT(*) 数行，COUNT(CASE WHEN ... THEN 1 END) 或者加 WHERE 都能数 C。两条语句也行，但一条更利索。

说明

MySQL 终端sql

SELECT COUNT(*) AS 行数, SUM(level = 'C') AS 等级C
FROM lesson15_constraint;

输出示例（只读，不要粘贴执行）text不可复制

+--------+---------+
| 行数   | 等级C   |
+--------+---------+
|      4 |       2 |
+--------+---------+
1 row in set (0.00 sec)

SUM(level = 'C') 把“是 C”算成 1、“不是”算成 0，加起来就是个数。行数 4 里有 2 个 C（小甲、小乙）。

说明

写 WHERE level = 'C' 又同时 COUNT(*)，结果只剩 2——那是“C 有几个人”，不是“一共几个人”。

2. 再插一个年龄为 0 的人，应该被拒绝。写出语句并说明是哪个约束拦的。

说明

age > 0 是严格大于，0 不满足。

说明

MySQL 终端sql

INSERT INTO lesson15_constraint (name, age) VALUES ('小辛', 0);

输出示例（只读，不要粘贴执行）text不可复制

ERROR 3819 (HY000): Check constraint 'ck_lesson15_age' is violated.

3819，拦它的是 ck_lesson15_age。若把条件改成 age >= 0，0 就能进——这是规则设计问题，不是数据库问题。

说明

以为 CHECK 会顺带挡住 NULL。它不会：NULL > 0 是未知，算通过。要挡空值得加 NOT NULL。

**3. 主键和 UNIQUE 都要求不重复，它们差在哪？**

说明

主键 = 非空 + 唯一，一张表只能有一个，用来标识“这一行是谁”；UNIQUE 只要求不重复，允许 NULL（多个 NULL 不算重复），一张表可以有多个。本节 id 是主键，name 是 UNIQUE——名字可以不填吗？不行，因为 name 另外还写了 NOT NULL；如果只写 UNIQUE，多个人不填名字是被允许的。

4. 约束能不能代替程序里的全部校验？举一个约束管不了的例子。

说明

不能。约束擅长单表单行、能用表达式说清的规则（非空、唯一、范围、引用）。需要看别的行或别的表才能判断的规则它管不了，比如“同一部门同时只能有一个主管”——这要数该部门里还有没有别的在职主管，是跨行判断；“员工的部门必须在职部门列表里”这种要跨表的状态组合也一样。这些仍要程序层校验。

本单元验收

做到下面 5 条，这一节就完成了。每条都给出你应该看到的真实结果。

SELECT COUNT(*) FROM lesson15_constraint; 是 4；GROUP_CONCAT(id ORDER BY id) 是 1,2,3,6（4 号被一次失败的插入吃掉、5 号是小戊且已删，AUTO_INCREMENT 不回填）。
level = 'C' 的有 2 行——前两条插入没写 level，DEFAULT 生效。
重复 name 报 1062、name 为 NULL 报 1048、age = 130 报 3819，三次失败后行数仍是 3（最后加上 小己 才是 4）。
能说清 CHECK 为什么挡不住 NULL（未知按通过），以及要挡空值该用 NOT NULL。
information_schema.TABLE_CONSTRAINTS 里能看到 ck_lesson15_age / PRIMARY / uk_lesson15_name 三条，并说出给约束起名的用处。

做到这里

做到这里可以停。你现在能读懂一张表的列属性和约束清单，能故意违反约束并对着错误码说清是哪条规则在拦，也知道 CHECK 和 NOT NULL 各管一段。

下一节讲第六类约束：外键——两张表怎么拴在一起，以及父表被删被改时子表会怎么反应。

来源与边界

本节对应 BV1Kr4y1i7ru 平台 part 32「32. 基础-约束-概述」、part 33「33. 基础-约束-演示」（P32–P33，在 P01–P50 范围内），主题与同版讲义《MySQL-基础篇》4.1（p61–62）、4.2（p62–64）对应；讲义为公开镜像，仅作主题与字段语义核对，本节未复制讲义正文。

无逐字稿，本节不声称听过讲解、不引用原话、不写时间戳。讲义 4.2 的演示围绕一张带自增主键、非空唯一姓名、年龄范围、状态默认值的表展开；本节自建 lesson15_constraint，字段语义对应（id / name / age / level，用 level 代替课堂里的状态位），表名、行数据、查询与练习均为本教程原创。

本节每段 text 输出都在本机隔离的 MySQL 8.0.46 实例上逐条实跑（与学习服务器同版本号，不是同一台机器）；0.00 sec 是测试机耗时，你屏幕上可能是 0.01 sec，行列内容一致。CHECK 需要 8.0.16 以上才真正生效，学习服务器是 8.0.46，满足条件。页面不含 DROP DATABASE / DROP TABLE / TRUNCATE / 整库重置语句；重跑靠 SHOW TABLES LIKE 'lesson15_%'; 和行数核对，不需要删表。