约束与完整性

这一课你将能做到

1. 在建表与改表时正确声明 PRIMARY KEY、UNIQUE、NOT NULL、DEFAULT，并解释唯一约束与唯一索引是同一件事
2. 创建外键并实测 RESTRICT / CASCADE / SET NULL 三种 ON DELETE 行为的差异
3. 用 CHECK 约束（8.0.16+ 生效）挡住非法数据，并说出大厂为什么经常不用物理外键

为什么要学

应用层的校验可以绕过——脚本直连数据库、另一个服务、运维手改数据，都绕过你的 Spring 校验注解。约束是数据库在存储层的最后防线：脏数据根本进不来，而不是进来了等着清洗。约束也是面试的雷区：「为什么你们不用外键？」「自增主键和 UUID 怎么选？」答不好，说明没在真实系统里做过数据一致性。

人话：约束就是数据库的「门禁系统」。NOT NULL 查有没有带工牌，UNIQUE 查有没有冒名顶替，外键查部门对不对得上，CHECK 查数值合不合法——全过才放行。

先建立图像

完整性的分层防线（从外到内越来越严格）：

第 1 层  数据类型      runtime_ms 不可能存进 'abc'
第 2 层  NOT NULL      关键字段不许空
第 3 层  UNIQUE/PK     身份不许重复（uk_email、uk_username）
第 4 层  FOREIGN KEY   引用的对象必须真实存在
第 5 层  CHECK         值必须满足业务规则（runtime_ms >= 0）
第 6 层  应用层/触发器  复杂业务规则（余额不可为负等）

外键的三种 ON DELETE 行为：

父行(cf_user)被删除时，子行(cf_comment)怎么办？
RESTRICT → 拒绝删除（ERROR 1451）        「有人引用你，不许走」
CASCADE  → 子行跟着删                    「陪葬」
SET NULL → 子行的外键列置 NULL            「断开关系，各自安好」

跟着做

步骤 0：数据准备

USE sql_practice;
TRUNCATE TABLE cf_submission;
TRUNCATE TABLE cf_practice_record;
TRUNCATE TABLE cf_user;
TRUNCATE TABLE cf_problem;
-- 执行第 1 课步骤 4 的四段 INSERT，恢复初始数据

步骤 1：主键与唯一约束实战

-- 唯一键冲突（cf_problem.slug 已有 uk_slug）
INSERT INTO cf_problem (slug, title) VALUES ('two-sum', '重复题');
-- ERROR 1062 Duplicate entry 'two-sum' for key 'uk_slug'

-- NOT NULL 违反
INSERT INTO cf_user (username, email) VALUES (NULL, 'x@x.com');
-- ERROR 1048 Column 'username' cannot be null

再验证「唯一约束允许多个 NULL」：

CREATE TABLE t_tag (
  id   INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(50) UNIQUE
) ENGINE=InnoDB;

INSERT INTO t_tag (name) VALUES ('dp'), (NULL), (NULL);
SELECT * FROM t_tag;   -- 两个 NULL 都插入成功
DROP TABLE t_tag;

InnoDB 视 NULL 为「未知」，未知与未知不冲突，所以唯一索引列可以有多行 NULL——判断「是否已设置」要靠 IS NULL，而不是靠唯一约束。

步骤 2：外键之 RESTRICT——默认的「不许删」

CREATE TABLE cf_comment (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NOT NULL,
  content    VARCHAR(500) NOT NULL,
  created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  CONSTRAINT fk_comment_user
    FOREIGN KEY (user_id) REFERENCES cf_user (id)
    ON DELETE RESTRICT
) ENGINE=InnoDB;

INSERT INTO cf_comment (user_id, content) VALUES (1, '这道题解法很妙');

DELETE FROM cf_user WHERE id = 1;
-- ERROR 1451 Cannot delete or update a parent row:
-- a foreign key constraint fails (`sql_practice`.`cf_comment`, CONSTRAINT `fk_comment_user`)

报错信息直接点名了失败的约束名——这就是第 2 课强调约束命名规范的原因。

步骤 3：外键之 SET NULL 与 CASCADE

CREATE TABLE cf_feedback (
  id         BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id    BIGINT UNSIGNED NULL COMMENT '可空：匿名反馈',
  content    VARCHAR(500) NOT NULL,
  CONSTRAINT fk_feedback_user
    FOREIGN KEY (user_id) REFERENCES cf_user (id)
    ON DELETE SET NULL          -- 删用户后反馈保留，user_id 变 NULL
) ENGINE=InnoDB;

INSERT INTO cf_feedback (user_id, content) VALUES (2, '希望增加 Rust 语言');

-- SET NULL 演示：先给 cf_comment 换成 SET NULL 行为的副本
CREATE TABLE cf_comment2 LIKE cf_comment;
-- （结构复制不复制外键的 ON DELETE，改用直接建表验证）
DROP TABLE cf_comment2;
CREATE TABLE cf_comment2 (
  id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
  user_id BIGINT UNSIGNED NULL,
  content VARCHAR(500) NOT NULL,
  CONSTRAINT fk_comment2_user FOREIGN KEY (user_id)
    REFERENCES cf_user (id) ON DELETE SET NULL
) ENGINE=InnoDB;
INSERT INTO cf_comment2 (user_id, content) VALUES (1, '测试 SET NULL');

DELETE FROM cf_feedback WHERE user_id = 2;  -- 先清理，避免干扰
-- 直接删除被 cf_comment 引用的 user 1 仍会失败（RESTRICT 挡着）
DELETE FROM cf_user WHERE id = 1;           -- ERROR 1451

-- 摘掉 RESTRICT 的引用后，SET NULL 生效
DELETE FROM cf_comment WHERE user_id = 1;
DELETE FROM cf_user WHERE id = 1;
SELECT user_id, content FROM cf_comment2;   -- user_id 已变 NULL，评论还在

CASCADE 同理会「连坐删除」子行——权限交给设计者，责任也在设计者，生产上慎用级联删除。

步骤 4：CHECK 约束（8.0.16+）

ALTER TABLE cf_submission
  ADD CONSTRAINT chk_runtime_nonneg CHECK (runtime_ms >= 0);

INSERT INTO cf_submission (user_id, problem_id, status, runtime_ms)
VALUES (1, 1, 'accepted', -5);
-- ERROR 3819 Check constraint 'chk_runtime_nonneg' is violated.

ALTER TABLE cf_submission DROP CHECK chk_runtime_nonneg;

CHECK 8.0.16 之前只解析不生效（老文章说「MySQL 的 CHECK 没用」就是那个年代的事），8.0.16 起真正拦数据。它不能引用其他行、不能用子查询、不能用存储函数——只做「本行内」的规则。

步骤 5：查看约束全景

SHOW CREATE TABLE cf_comment\G

SELECT TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'sql_practice'
ORDER BY TABLE_NAME;

-- 外键列与引用列的类型必须逐项一致，否则建外键报 ERROR 3780

步骤 6：清理演示表

DROP TABLE cf_comment;    -- 先删子表
DROP TABLE cf_feedback;
-- 演示表清掉，避免残留外键影响后续课程的 TRUNCATE

讲清楚要点

1. 主键选择的三个候选：自增 BIGINT（顺序写入、页不分裂、索引紧凑，是默认选择）；业务字段（手机号、身份证——会变更，坚决不用）；UUID/雪花 ID（分布式友好，但 UUID 完全乱序会导致 InnoDB 页分裂与写放大，用也要用有序的 UUIDv7/雪花）。面试答「默认自增，分布式场景用有序 ID」。
2. 唯一约束 = 唯一索引：MySQL 里 UNIQUE 约束就是靠唯一索引实现的，没有「只有约束没有索引」的形态。upsert（ON DUPLICATE KEY UPDATE）依赖它，业务幂等也依赖它。
3. 外键是一把双刃剑：好处是数据库层强制一致性、报错早；代价是写放大（每次写入都要查父表）、锁链（父行锁传染到子表）、迁移/分库分表困难。所以互联网公司普遍应用层维护引用关系 + 定时对账任务兜底，金融等强一致场景才用物理外键。
4. ON DELETE 行为要在设计期定死：RESTRICT（默认，保守）、CASCADE（连坐，慎用）、SET NULL（列必须可空）、NO ACTION（MySQL 中等同 RESTRICT）。设计评审时「删用户后他的提交怎么办」必须有明确答案。
5. CHECK 管本行、外键管跨表、触发器管历史：三者的适用范围不要混用。CHECK 8.0.16+ 生效且不支持子查询/自定义函数；复杂规则上应用层。
6. 先清数据再加约束：给存量表加唯一约束/外键前，先查出脏数据（GROUP BY ... HAVING COUNT(*) > 1），否则加约束的 DDL 直接失败或加载半截。

你可能会懵的地方

• 唯一索引列能存几个 NULL？ 任意多个。NULL 表示未知，未知与未知不冲突。如果业务要求「唯一且必填」，约束组合是 NOT NULL + UNIQUE。

• 外键列必须建索引吗？ InnoDB 会自动给外键列建索引（如果没有）。子表写入要检查父表存在、父表删除要反查子表，索引是硬需求；显式建索引能控制其命名与顺序。

• 为什么大厂普遍不开外键？ 高并发写入时外键检查是额外开销；锁会沿父子表传染（删父表行锁子表扫描）；分库分表后父子表可能不在一个实例，物理外键根本建不了。一致性靠应用层事务 + 定时对账兜底。

• TRUNCATE 能绕过外键吗？ 不能，有子表引用时 TRUNCATE 父表报 ERROR 1701（TRUNCATE 不走逐行 DELETE）。批量重建数据的土办法：SET FOREIGN_KEY_CHECKS=0; 清完再开——只在自己可控的环境用。

• ENUM 值不在列表里会怎样？ 严格模式（8.0 默认）报 ERROR 1265 拒绝写入；非严格模式截断成 ''。所以 ENUM 也算一种约束，但扩展枚举值需要 ALTER，业务膨胀快时用字典表。

错法对照

错法	问题	正解
外键列与被引用列类型不一致	ERROR 3780 建不起来	两侧类型、符号、长度完全一致
用手机号/邮箱当主键	业务值会变更，且拖累所有关联表	无意义自增/有序 ID 当主键
给写入密集的大表随手加外键	写放大 + 锁传染	应用层校验 + 对账任务
ON DELETE CASCADE 挂满全链	一个误删级联清库	关键链路 RESTRICT，删数据走显式流程
存量脏数据没清就加唯一约束	DDL 失败或冲突报错	先 GROUP BY 找重、清理、再加约束

卡点急救

1. ERROR 1451 Cannot delete or update a parent row：有子表引用。要么先删/改子表引用，要么确认业务后把外键行为改成 SET NULL/CASCADE。
2. ERROR 1452 Cannot add or update a child row：引用的父行不存在。先插入父表记录，再插子表；导入脚本注意顺序。
3. ERROR 3819 Check constraint violated：数据违反 CHECK 规则。修正数据；规则本身定错了就 DROP CHECK 后重建。
4. ERROR 1701 Cannot truncate a table referenced in a foreign key constraint：先 TRUNCATE/清理子表，或临时 SET FOREIGN_KEY_CHECKS=0（用完必须恢复 1）。

面试常问

• 为什么很多公司不用物理外键？ 性能（写放大）、锁传染、分库分表不可行三个原因；一致性由应用层事务 + 异步对账保证。回答时补一句「强一致金融场景仍会用」体现权衡。

• 自增主键 vs UUID？ 自增：有序插入、页顺序写、索引紧凑；缺点是分库分表冲突、暴露业务量。UUID：全局唯一、可离线生成；缺点是无序导致 InnoDB 页分裂、主键 36 字节索引膨胀。折中：雪花 ID/UUIDv7 这类趋势递增的分布式 ID。

• 数据库约束和应用层校验怎么分工？ 应用层管用户体验与复杂规则（提前返回友好错误）；数据库约束是兜底防线，防绕过 API 的脏写。两层都要有，不是二选一。

自测三问

1. 唯一索引列插入两个 NULL 会发生什么？为什么？
2. ON DELETE 的 RESTRICT、CASCADE、SET NULL 分别适合什么业务场景？
3. 公司不开物理外键后，用什么手段保证引用完整性？说出至少两种。

小结一句话

约束是数据质量的最后一道门——主键定身份、唯一防重复、外键管引用、CHECK 管本行规则；用不用物理外键是性能与一致性的权衡，必须能说出取舍理由。

延伸

• MySQL 8.0 Reference Manual — Constraint Enforcement
• MySQL 8.0 Reference Manual — FOREIGN KEY Constraints
• 下一课：事务与安全——ACID、隔离级别与权限体系，并发世界的正确性守则。