第8章：约束详解

本章目标

学完本章后，你将能够：

掌握 PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、DEFAULT、CHECK 六种约束的语法和用途
正确配置外键的级联操作（CASCADE/SET NULL/RESTRICT），理解其后果
了解 InnoDB 聚簇索引的基本概念及其与主键的关系
在 MySQL 8.0 中正确使用 CHECK 约束（注意 5.7 的静默忽略陷阱）
学会查看、管理、临时禁用约束

前置知识

第7章：会创建表、ALTER TABLE 的基本操作
第5章：理解数据类型

8.1 什么是约束，为什么需要约束

约束（Constraint） 是定义在表的列或表级上的规则，用于限制存入表中的数据，确保数据的正确性、完整性和有效性。

如果没有约束会发生什么

想象一个用户表中没有约束：

sql

CREATE TABLE no_constraints (
    id INT,
    username VARCHAR(20),
    email VARCHAR(50),
    age INT
);

-- 以下是"合法"的插入（因为没有任何约束阻止它们）
INSERT INTO no_constraints VALUES (1, 'alice', 'alice@example.com', 25);   -- 正常
INSERT INTO no_constraints VALUES (1, 'bob', 'alice@example.com', 25);     -- 重复 ID
INSERT INTO no_constraints VALUES (NULL, NULL, NULL, NULL);                   -- 全是 NULL
INSERT INTO no_constraints VALUES (2, 'charlie', 'not-an-email', -5);       -- 年龄为负

没有约束的数据表就像一个没有门禁的建筑——谁都能进来，什么都能放。随着数据的累积，脏数据、重复数据、不一致数据会越来越多，最终导致：

查询结果不可信（同一个用户出现在多个行中）
业务逻辑需要在应用层重复校验（容易遗漏）
数据清理成本高昂

约束的分类

约束类型	作用	列级	表级
PRIMARY KEY	唯一标识每一行，自动 NOT NULL	✅	✅
FOREIGN KEY	保证引用完整性（子表的值必须在父表中存在）	❌	✅
UNIQUE	保证列值（或列组合）的唯一性	✅	✅
NOT NULL	保证列不能包含 NULL	✅	❌（只能列级）
DEFAULT	为列提供默认值	✅	❌（只能列级）
CHECK	自定义条件校验（如 age >= 0）	✅	✅（MySQL 8.0+）

为什么约束应该在数据库层面而不只是应用层面：应用层面的校验可以被绕过（直接操作数据库、数据迁移、多应用共享数据库）。数据库层面的约束是"最后一道防线"，无论数据从哪个入口进来都必须遵守。

8.2 PRIMARY KEY（主键约束）

基本定义

主键是表中最核心的约束，它唯一标识表中的每一行。

sql

-- 列级定义（单列主键）
CREATE TABLE users1 (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

-- 表级定义（等价效果）
CREATE TABLE users2 (
    id INT,
    name VARCHAR(50),
    PRIMARY KEY (id)
);

-- 复合主键（只能用表级定义）
CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT NOT NULL,
    PRIMARY KEY (order_id, product_id)
);
-- 含义：同一订单中，同一个产品只能出现一次（只关心有没有，数量在 quantity 体现）

主键的隐含规则

sql

-- 规则1：主键自动 NOT NULL
CREATE TABLE pk_test (
    id INT PRIMARY KEY
);
DESC pk_test;
-- id 列的 Null 为 NO（自动设置为 NOT NULL）

-- 规则2：一张表只能有一个主键（但可以是复合主键）
CREATE TABLE multi_pk (
    id1 INT PRIMARY KEY,
    id2 INT PRIMARY KEY    -- ❌ ERROR 1068: Multiple primary key defined
);

-- 规则3：主键值不能重复
INSERT INTO pk_test VALUES (1), (2), (3);  -- ✅
INSERT INTO pk_test VALUES (1);            -- ❌ ERROR 1062: Duplicate entry '1' for key 'PRIMARY'

-- 规则4：主键值不能为 NULL
INSERT INTO pk_test VALUES (NULL);         -- ❌ ERROR 1048: Column 'id' cannot be null

添加和删除主键

sql

-- 表已存在时添加主键
ALTER TABLE users ADD PRIMARY KEY (id);

-- 复合主键
ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);

-- 删除主键（先删自增，再删主键）
-- 如果你的主键是 AUTO_INCREMENT，必须先 MODIFY 去掉 AUTO_INCREMENT
ALTER TABLE users MODIFY COLUMN id INT NOT NULL;  -- 去掉 AUTO_INCREMENT
ALTER TABLE users DROP PRIMARY KEY;
-- 注意：DROP PRIMARY KEY 不会删除该列！只是取消主键约束

主键与 InnoDB 聚簇索引

这是理解 MySQL 性能的基石之一。

在 InnoDB 存储引擎中，表数据按照主键的顺序物理存储。这种以主键为排序依据的数据组织方式称为聚簇索引（Clustered Index）。

聚簇索引示意（B+树叶子节点就是数据行）：

        [1-50]
       /      \
    [1-25]    [26-50]
    /    \     /    \
 [数据行] [数据行] [数据行] [数据行]

这意味着：

按主键查询是最快的（主键 → 直接定位到数据行）
按主键顺序插入性能最好（新数据追加到末尾，不会引起页分裂）
主键不宜过大（主键会被存储到每个二级索引中作为指向行的"指针"）
推荐使用自增整数作为主键（自增 → 顺序插入，整数 → 小且快）

sql

-- ✅ 推荐：自增整数主键
id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY

-- ⚠️ 不推荐但可用：UUID 主键（非顺序插入会导致页分裂和碎片）
id CHAR(36) PRIMARY KEY  -- UUID v4 是随机的，插入性能较差

-- ❌ 不推荐：业务数据做主键（如身份证号、手机号）
-- 问题：太长的值让二级索引膨胀；业务变更会导致主键变更（非常危险）

8.3 FOREIGN KEY（外键约束）

基本概念

外键建立了两张表之间的父子关系。子表中的外键列的值必须在父表的主键（或唯一键）中存在。

sql

-- 父表
CREATE TABLE departments (
    id   INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

-- 子表（包含外键）
CREATE TABLE employees (
    id        INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
    name      VARCHAR(50) NOT NULL,
    dept_id   INT UNSIGNED NOT NULL,
    FOREIGN KEY (dept_id) REFERENCES departments(id)
    -- employees.dept_id 必须在 departments.id 中存在
);

-- 先插入父表
INSERT INTO departments VALUES (1, '技术部'), (2, '市场部'), (3, '人事部');

-- 插入合法外键值
INSERT INTO employees (name, dept_id) VALUES ('Alice', 1), ('Bob', 2);
-- ✅ 成功：dept_id = 1 和 2 在 departments 中存在

-- 插入不存在的 dept_id
INSERT INTO employees (name, dept_id) VALUES ('Charlie', 99);
-- ❌ ERROR 1452 (23000): Cannot add or update a child row:
--    a foreign key constraint fails

级联操作：ON DELETE 和 ON UPDATE

这是外键最强大的部分——定义当父表数据被删除或更新时，子表数据如何变化：

级联选项	ON DELETE 效果	ON UPDATE 效果	使用场景
CASCADE	父行删除 → 子行也自动删除	父键更新 → 子键也自动更新	强关联（如订单-订单明细）
SET NULL	父行删除 → 子键设为 NULL	父键更新 → 子键设为 NULL	可选关联（如"可选部门"）
RESTRICT	父行如果有子行 → 阻止删除	父键如果有子行 → 阻止更新	需要保护的数据（默认）
NO ACTION	同 RESTRICT（MySQL 中）	同 RESTRICT	标准 SQL 的默认值
SET DEFAULT	设为默认值	设为默认值	MySQL 解析但不支持（InnoDB 会报错）

级联操作示例

sql

-- 准备父表和子表
CREATE TABLE dept (
    id   INT PRIMARY KEY,
    name VARCHAR(50)
);

INSERT INTO dept VALUES (1, '技术部'), (2, '市场部'), (3, '人事部');

-- 1. CASCADE 示例：删除部门时，员工自动删除
CREATE TABLE emp_cascade (
    id      INT PRIMARY KEY,
    name    VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES dept(id) ON DELETE CASCADE ON UPDATE CASCADE
);

INSERT INTO emp_cascade VALUES
    (1, 'Alice', 1), (2, 'Bob', 2), (3, 'Charlie', 1);

-- 删除技术部（id=1）
DELETE FROM dept WHERE id = 1;
-- 查看员工表：Alice 和 Charlie 也自动被删除了！
SELECT * FROM emp_cascade;
-- +----+------+---------+
-- | id | name | dept_id |
-- +----+------+---------+
-- |  2 | Bob  |       2 |
-- +----+------+---------+

-- 2. SET NULL 示例：删除部门时，员工的 dept_id 变为 NULL
CREATE TABLE emp_setnull (
    id      INT PRIMARY KEY,
    name    VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES dept(id) ON DELETE SET NULL ON UPDATE SET NULL
);

INSERT INTO emp_setnull VALUES (1, 'Alice', 2), (2, 'Bob', 3);

DELETE FROM dept WHERE id = 2;

SELECT * FROM emp_setnull;
-- +----+-------+---------+
-- | id | name  | dept_id |
-- +----+-------+---------+
-- |  1 | Alice |    NULL |  ← 部门删除后变为 NULL
-- |  2 | Bob   |       3 |
-- +----+-------+---------+

-- 3. RESTRICT 示例：如果部门有员工，阻止删除
CREATE TABLE emp_restrict (
    id      INT PRIMARY KEY,
    name    VARCHAR(50),
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES dept(id) ON DELETE RESTRICT
);

INSERT INTO emp_restrict VALUES (1, 'Alice', 3);

DELETE FROM dept WHERE id = 3;
-- ❌ ERROR 1451: Cannot delete or update a parent row:
--    a foreign key constraint fails
-- 必须先删除或转移员工，再删除部门

外键的要求与限制

sql

-- 要求1：父表被引用的列必须有 UNIQUE 或 PRIMARY KEY 约束
CREATE TABLE bad_parent (
    dept_id INT          -- 没有索引
);
CREATE TABLE bad_child (
    id INT,
    dept_id INT,
    FOREIGN KEY (dept_id) REFERENCES bad_parent(dept_id)
);
-- ❌ ERROR 1822: Failed to add the foreign key constraint.
--    Missing index for constraint in referenced table 'bad_parent'

-- 要求2：父列和子列必须是相同的数据类型（包括 UNSIGNED 修饰符！）
CREATE TABLE parent_unsigned (
    id INT UNSIGNED PRIMARY KEY   -- UNSIGNED
);
CREATE TABLE child_signed (
    id INT,
    parent_id INT,                -- 有符号 INT，与父列不匹配！
    FOREIGN KEY (parent_id) REFERENCES parent_unsigned(id)
);
-- ❌ ERROR 3780 (HY000): Referencing column 'parent_id' and referenced
--    column 'id' in foreign key constraint are incompatible

-- 要求3：子表的存储引擎必须支持外键（MyISAM 不支持！）
CREATE TABLE parent_innodb (id INT PRIMARY KEY) ENGINE=InnoDB;
CREATE TABLE child_myisam (
    id INT,
    parent_id INT,
    FOREIGN KEY (parent_id) REFERENCES parent_innodb(id)
) ENGINE=MyISAM;
-- ⚠️ 不会报错，但外键定义被静默忽略！MyISAM 不支持外键

查看约束名称和删除外键

sql

-- 查看外键的名字
SHOW CREATE TABLE emp_cascade\G
-- 你会看到类似：
-- CONSTRAINT `emp_cascade_ibfk_1` FOREIGN KEY (`dept_id`) REFERENCES `dept` (`id`) ...

-- 如果建表时没指定名字，MySQL 会生成自动名字（如 emp_cascade_ibfk_1）
-- 删除外键需要知道约束名
ALTER TABLE emp_cascade DROP FOREIGN KEY emp_cascade_ibfk_1;

-- ✅ 推荐：建表时显式指定外键名
CREATE TABLE emp_named (
    id      INT PRIMARY KEY,
    dept_id INT,
    CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES dept(id)
);
-- 之后操作就用 fk_emp_dept 这个人类可读的名字

外键的性能影响

外键不是免费的午餐：

INSERT/UPDATE 子表时：MySQL 需要在父表中查找，确认外键值存在（如果父表有索引，这是一次索引查找，开销不大）
DELETE/UPDATE 父表时：MySQL 需要检查子表中是否有引用（子表的外键列上必须建索引，否则会全表扫描！）
外键列建议建立索引：否则 CASCADE 操作会做全表扫描

sql

-- 当你定义外键时，MySQL 不会自动在子表外键列上创建索引！
-- 需要手动创建：
ALTER TABLE emp_cascade ADD INDEX idx_dept_id (dept_id);

何时不使用外键

在以下场景中，很多团队选择在应用层维护引用完整性，而不是用数据库外键：

超高并发写入：每次 INSERT 都校验父表会增加锁竞争
分库分表：数据分布在多个 MySQL 实例上，无法跨实例创建外键
历史遗留系统：数据已经有很多不一致，无法修复后再加外键
数据仓库/分析系统：数据主要用于查询分析，写入不频繁

取舍提醒：放弃外键意味着你要在应用层实现所有引用完整性校验。虽然技术上是可行的，但要求你比 MySQL 更细心地处理数据一致性问题。

8.4 UNIQUE 约束

UNIQUE 保证列（或列的组合）中不会出现重复值。

sql

-- 单列唯一约束
CREATE TABLE users (
    id       INT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) UNIQUE,         -- 列级定义
    email    VARCHAR(100),
    UNIQUE KEY uk_email (email)          -- 表级定义（带索引名）
);

INSERT INTO users (username, email) VALUES ('alice', 'alice@example.com');
INSERT INTO users (username, email) VALUES ('alice', 'another@example.com');
-- ❌ ERROR 1062: Duplicate entry 'alice' for key 'username'

-- 复合唯一约束
CREATE TABLE daily_attendance (
    employee_id INT NOT NULL,
    date        DATE NOT NULL,
    status      ENUM('present', 'absent', 'late', 'leave') NOT NULL,
    UNIQUE KEY uk_emp_date (employee_id, date)
    -- 确保同一个员工在同一天只能有一条考勤记录
);

INSERT INTO daily_attendance VALUES (1, '2026-07-09', 'present');
INSERT INTO daily_attendance VALUES (1, '2026-07-09', 'absent');
-- ❌ ERROR 1062: Duplicate entry '1-2026-07-09' for key 'uk_emp_date'

UNIQUE 允许空值（且允许多个 NULL！）

这是 MySQL 不同于某些数据库的行为：

sql

CREATE TABLE unique_null_test (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(20) UNIQUE
);

INSERT INTO unique_null_test (code) VALUES (NULL);
INSERT INTO unique_null_test (code) VALUES (NULL);
INSERT INTO unique_null_test (code) VALUES (NULL);
-- ✅ 全部插入成功！
-- MySQL 认为 NULL != NULL（即两个 NULL 不视为"重复"）

SELECT * FROM unique_null_test;
-- +----+------+
-- | id | code |
-- +----+------+
-- |  1 | NULL |
-- |  2 | NULL |
-- |  3 | NULL |
-- +----+------+

注意：在 SQL 标准中，NULL 表示"未知"，NULL 和 NULL 的比较结果为 UNKNOWN（既不是 TRUE 也不是 FALSE）。所以 MySQL 允许在 UNIQUE 列中插入多个 NULL。但 utf8mb4_bin 排序规则下的 UNIQUE 在某些行为上可能有所不同。

UNIQUE vs PRIMARY KEY

维度	PRIMARY KEY	UNIQUE
NULL 支持	不允许	允许（且允许多个 NULL）
每表数量	最多 1 个	可以有多个
自动创建索引	是（聚簇索引，InnoDB 中）	是（普通二级索引）
本质	唯一标识 + 非空	纯唯一约束
可否作为外键引用	可以	可以

8.5 NOT NULL 与 DEFAULT 约束

NOT NULL

sql

-- 列默认是 NULL 的（即可以不提供值）
CREATE TABLE null_demo (
    a INT,              -- 默认 NULL
    b INT NOT NULL      -- 不能为 NULL
);

INSERT INTO null_demo (a, b) VALUES (1, 2);    -- ✅
INSERT INTO null_demo (a, b) VALUES (NULL, 3);  -- ✅ a 可以是 NULL
INSERT INTO null_demo (a) VALUES (1);           -- ❌ 没有给 b 提供值，且 b 没有 DEFAULT
INSERT INTO null_demo (b) VALUES (5);           -- ✅ a 不提供值，默认为 NULL

-- 使用场景判断
-- 应该 NOT NULL 的：id、姓名、创建时间、金额
-- 可以为 NULL 的：中间名、备注、可选邮箱

DEFAULT

当 INSERT 没有为列提供值时，如果该列有 DEFAULT，则使用默认值：

sql

CREATE TABLE default_demo (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    username    VARCHAR(50) NOT NULL,
    status      TINYINT NOT NULL DEFAULT 1,             -- 默认 1（启用）
    role        VARCHAR(20) NOT NULL DEFAULT 'user',     -- 默认 'user'
    created_at  TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- DEFAULT 也适用于可以为 NULL 的列
    avatar_url  VARCHAR(200) NULL DEFAULT NULL
);

-- 插入时可以不指定有 DEFAULT 的列
INSERT INTO default_demo (username) VALUES ('Alice');

SELECT * FROM default_demo;
-- +----+----------+--------+------+---------------------+------------+
-- | id | username | status | role | created_at          | avatar_url |
-- +----+----------+--------+------+---------------------+------------+
-- |  1 | Alice    |      1 | user | 2026-07-09 15:00:00 | NULL       |
-- +----+----------+--------+------+---------------------+------------+

-- 可以显式覆盖默认值
INSERT INTO default_demo (username, status, role) VALUES ('Bob', 0, 'admin');

-- DEFAULT 必须是常量或 CURRENT_TIMESTAMP（不能是函数调用或子查询！）
CREATE TABLE bad_default (
    id INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT NOW()  -- MySQL 中 CURRENT_TIMESTAMP 可以用括号，
                                          -- 但大多数函数用不了！
);

8.6 CHECK 约束（MySQL 8.0+ 的重大更新）

CHECK 约束是 MySQL 8.0.16 才真正支持的特性。在此之前（MySQL 5.7 及更早），MySQL 解析 CHECK 语法但不执行它——这是一个巨大的陷阱！

CHECK 约束语法

sql

-- 列级 CHECK
CREATE TABLE check_demo (
    id   INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age  INT NOT NULL CHECK (age >= 0 AND age <= 150),          -- 年龄范围
    email VARCHAR(100) CHECK (email LIKE '%@%.%'),               -- 简单邮箱校验
    score INT CHECK (score >= 0 AND score <= 100)
);

-- 表级 CHECK（可以引用多个列）
CREATE TABLE products (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL,
    price       DECIMAL(10, 2) NOT NULL,
    sale_price  DECIMAL(10, 2) NULL,
    CONSTRAINT chk_price_positive CHECK (price > 0),
    CONSTRAINT chk_sale_less_than_price CHECK (sale_price IS NULL OR sale_price < price)
);

-- 测试 CHECK 是否生效
INSERT INTO check_demo (name, age) VALUES ('Alice', 25);   -- ✅
INSERT INTO check_demo (name, age) VALUES ('Bob', -5);     -- ❌ 应该报错
-- MySQL 8.0.16+: ERROR 3819 (HY000): Check constraint 'check_demo_chk_1' is violated.
-- MySQL 5.7: 静默接受！（CHECK 被解析但被忽略）

MySQL 5.7 的 CHECK 陷阱

这是 MySQL 历史上最坑的特性之一。在 5.7 中：

sql

-- MySQL 5.7 中运行以下代码
CREATE TABLE check_trap (
    id INT PRIMARY KEY,
    age INT CHECK (age >= 0 AND age <= 150)
);

INSERT INTO check_trap VALUES (1, -999);   -- ✅ 允许插入！
INSERT INTO check_trap VALUES (2, 5000);   -- ✅ 允许插入！
-- CHECK 被解析了（所以不报语法错误），但完全被忽略！
-- 数据显示 "CHECK 约束存在"，实际上完全不生效

SHOW CREATE TABLE check_trap\G
-- 你甚至还能看到 CHECK 子句在定义中，但它不工作！

如果在 5.7 中需要真正的 CHECK 功能，唯一的替代方案是触发器（Trigger）：

sql

DELIMITER //
CREATE TRIGGER trg_check_age BEFORE INSERT ON check_trap
FOR EACH ROW
BEGIN
    IF NEW.age < 0 OR NEW.age > 150 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '年龄必须在 0-150 之间';
    END IF;
END//
DELIMITER ;

重要提醒：升级到 MySQL 8.0+ 之后，之前"无害"的 CHECK 约束会突然开始生效，可能阻止后续的 INSERT/UPDATE！升级前记得排查历史数据中是否有违反 CHECK 约束的记录。

CHECK 约束的实际应用

sql

-- 状态值校验
CREATE TABLE orders (
    id        INT PRIMARY KEY AUTO_INCREMENT,
    status    VARCHAR(20) NOT NULL DEFAULT 'pending',
    amount    DECIMAL(10, 2) NOT NULL,
    qty       INT NOT NULL,
    CONSTRAINT chk_status CHECK (status IN ('pending', 'processing', 'shipped', 'delivered', 'cancelled')),
    CONSTRAINT chk_amount CHECK (amount > 0),
    CONSTRAINT chk_qty CHECK (qty > 0 AND qty <= 9999)
);

-- 逻辑一致性校验
CREATE TABLE date_range (
    id          INT PRIMARY KEY AUTO_INCREMENT,
    start_date  DATE NOT NULL,
    end_date    DATE NOT NULL,
    CONSTRAINT chk_date_order CHECK (end_date >= start_date),
    CONSTRAINT chk_date_range CHECK (DATEDIFF(end_date, start_date) <= 365)
    -- 日期范围不能超过一年
);

8.7 查看约束

方式一：SHOW CREATE TABLE

sql

-- 最直接的方式：查看完整建表语句
SHOW CREATE TABLE employees\G
-- 所有约束（PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK）都会出现在输出中

方式二：information_schema

sql

-- 查看表的所有约束
SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE
FROM information_schema.TABLE_CONSTRAINTS
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';
-- +-----------------+-----------------+
-- | CONSTRAINT_NAME | CONSTRAINT_TYPE |
-- +-----------------+-----------------+
-- | PRIMARY         | PRIMARY KEY     |
-- | fk_emp_dept     | FOREIGN KEY     |
-- | uk_email        | UNIQUE          |
-- | chk_age         | CHECK           |
-- +-----------------+-----------------+

-- 查看约束具体包含哪些列
SELECT CONSTRAINT_NAME, COLUMN_NAME, ORDINAL_POSITION
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = 'your_db' AND TABLE_NAME = 'your_table';
-- +-----------------+-------------+------------------+
-- | CONSTRAINT_NAME | COLUMN_NAME | ORDINAL_POSITION |
-- +-----------------+-------------+------------------+
-- | PRIMARY         | id          |                1 |
-- | fk_emp_dept     | dept_id     |                1 |
-- | uk_email        | email       |                1 |
-- +-----------------+-------------+------------------+

-- 查看 CHECK 约束的详细表达式
SELECT CONSTRAINT_NAME, CHECK_CLAUSE
FROM information_schema.CHECK_CONSTRAINTS
WHERE CONSTRAINT_SCHEMA = 'your_db';

8.8 禁用和启用外键检查

在某些场景下（如批量导入数据、数据迁移），临时禁用外键检查可以提高性能或打破循环依赖：

sql

-- 禁用外键检查（会话级别）
SET FOREIGN_KEY_CHECKS = 0;

-- 此时可以插入违反外键约束的数据
INSERT INTO employees (name, dept_id) VALUES ('Temp', 999);
-- ✅ 不会报错！外键检查被跳过了

-- 重新启用外键检查
SET FOREIGN_KEY_CHECKS = 1;

-- 启用后，MySQL 不会自动修复之前插入的不一致数据！
-- 你需要自己确保数据的一致性

慎用：SET FOREIGN_KEY_CHECKS = 0 是一个强大的开关，但它不会验证之前插入的数据是否正确。只在你知道自己在做什么的时候使用它。

另一种有用的设置是 UNIQUE 检查：

sql

-- 禁用唯一性检查（适用于 MyISAM，InnoDB 不支持此选项）
-- SET UNIQUE_CHECKS = 0;  -- 对 InnoDB 无效

-- 对于 InnoDB，批量导入要加快速度可以用：
SET autocommit = 0;
SET FOREIGN_KEY_CHECKS = 0;
-- 执行批量 INSERT
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
SET autocommit = 1;

8.9 约束命名规范建议

在团队开发中，统一的约束命名让维护和排错更容易：

约束类型	命名前缀	示例
PRIMARY KEY	`pk_` 或直接用 PRIMARY	`PRIMARY KEY (id)`（通常不需要自定义名）
FOREIGN KEY	`fk_`	`fk_employees_dept`
UNIQUE	`uk_`	`uk_users_email`
CHECK	`chk_`	`chk_age_range`
INDEX（普通索引）	`idx_`	`idx_users_name`（虽然索引不是约束）

sql

-- 带命名规范的完整建表示例
CREATE TABLE users (
    id        INT UNSIGNED NOT NULL AUTO_INCREMENT,
    username  VARCHAR(50)  NOT NULL,
    email     VARCHAR(100) NOT NULL,
    age       TINYINT UNSIGNED NOT NULL DEFAULT 18,
    dept_id   INT UNSIGNED NOT NULL,
    status    TINYINT NOT NULL DEFAULT 1,

    CONSTRAINT pk_users        PRIMARY KEY (id),
    CONSTRAINT uk_users_email  UNIQUE (email),
    CONSTRAINT uk_users_name   UNIQUE (username),
    CONSTRAINT fk_users_dept   FOREIGN KEY (dept_id) REFERENCES departments(id),
    CONSTRAINT chk_users_age   CHECK (age >= 0 AND age <= 150),
    CONSTRAINT chk_users_status CHECK (status IN (0, 1))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

-- 有名字后的优势：错误信息更清晰
-- 违反唯一约束时：
-- ERROR 1062 (23000): Duplicate entry 'alice@example.com' for key 'uk_users_email'
-- 一眼就知道是 users 表的 email 唯一键冲突

常见错误

错误 1：在 MySQL 5.7 中使用 CHECK 约束以为它有效

现象：在 MySQL 5.7 中建表用了 CHECK (age >= 0)，插入了 age = -1 也没报错。

原因：MySQL 5.7 解析 CHECK 语法但不执行它。这是 MySQL 历史上最著名的陷阱之一。

解决：

升级到 MySQL 8.0+
或在 5.7 中使用触发器模拟 CHECK 约束功能

错误 2：删除父表记录时未考虑级联后果

现象：

sql

DELETE FROM departments WHERE id = 1;
-- 如果子表外键是 ON DELETE CASCADE，技术部所有员工记录也被删除了！

原因：没有意识到 CASCADE 的破坏力——"一人犯事，株连九族"。

解决：

建外键时谨慎选择级联策略——你确定要 CASCADE 吗？大多数情况 RESTRICT 或 SET NULL 更安全
生产环境操作前，先在测试环境验证外键的级联行为
敏感数据的外键考虑软删除（设置 deleted_at 而非物理删除）

错误 3：UNIQUE 和 PRIMARY KEY 混淆

现象：有人认为 UNIQUE 就是 PRIMARY KEY，或者把两者互换使用。

原因：确实两者都能保证唯一性，但 PRIMARY KEY 有额外的语义和实现含义。

关键区别：

PRIMARY KEY 是唯一标识——代表"这一行是什么"
UNIQUE 是业务唯一性——代表"这个值不能重复"（如邮箱、身份证号）
PRIMARY KEY 在 InnoDB 中是聚簇索引，影响数据物理存储
每表只能有一个 PRIMARY KEY，但可以有多个 UNIQUE

错误 4：外键列数据类型不匹配导致 ALTER TABLE 失败

现象：

sql

ALTER TABLE child ADD CONSTRAINT fk_child_parent
    FOREIGN KEY (parent_id) REFERENCES parent(id);
-- ERROR 3780: Referencing column and referenced column are incompatible

原因：最常见的原因是两边的数据类型不完全一致，尤其是：

一边 INT，另一边 INT UNSIGNED（UNSIGNED 也算不一样的类型！）
一边 VARCHAR(50)，另一边 VARCHAR(100)

解决：确保引用的列和被引用列的数据类型完全一致，包括 UNSIGNED、长度等所有修饰符。

错误 5：忘记给外键列建索引

现象：定义了 ON DELETE CASCADE 的外键，但删除父表记录时非常慢。

原因：MySQL 不会自动在外键列上创建索引。当删除父行时，MySQL 需要扫描整个子表来找到引用该父行的所有子行。

解决：总是给外键列显式创建索引。

sql

ALTER TABLE child ADD INDEX idx_parent_id (parent_id);

本章练习

约束类型识别：根据以下需求，为每项要求选择最合适的约束类型：
- 订单号不能重复
- 每个身份证号只能注册一个账户
- 员工的部门必须在部门表中存在
- 商品价格不能为负数
- 创建时间在插入时自动填充
外键级联实验：创建 categories (id, name) 和 products (id, name, category_id, FOREIGN KEY ... ON DELETE CASCADE) 两张表。插入几条测试数据后删除 categories 中的某条记录，验证 products 中对应的商品是否也被删除。然后改用 ON DELETE SET NULL 重建，再次测试，观察差异。
CHECK 约束验证（需要 MySQL 8.0+）：创建包含以下 CHECK 约束的表：
- price DECIMAL(10,2) CHECK (price > 0)
- discount DECIMAL(3,2) CHECK (discount >= 0 AND discount <= 1)
- end_date DATE CHECK (end_date >= start_date) 尝试插入违反约束的数据，观察错误信息。如果你用的是 MySQL 5.7，描述 CHECK 在 5.7 中的行为。
UNIQUE 与 NULL 实验：创建一个包含 UNIQUE 列的表，尝试插入 3 行 NULL 值。解释为什么可以重复 NULL。然后在同一个 UNIQUE 列中插入一个非 NULL 的重复值，解释为什么不行。
information_schema 查询：写出一个 SQL 查询，从 information_schema 中列出某张表的所有约束信息，包括约束名称、类型、涉及的列名。
综合建表：设计一个"考试系统"的三张表，要求包含合理的约束：
- exams：考试 ID、考试名称、开始时间、结束时间、满分
- questions：题目 ID、所属考试 ID、题目内容、题型（单选/多选/判断/填空）、分值
- student_answers：答题记录 ID、学生 ID、题目 ID、学生答案、得分每张表必须包含至少 3 种不同类型的约束（PRIMARY KEY、FOREIGN KEY、UNIQUE、NOT NULL、CHECK、DEFAULT 中选）。

第8章：约束详解 ​

本章目标 ​

前置知识 ​

8.1 什么是约束，为什么需要约束 ​

如果没有约束会发生什么 ​

约束的分类 ​

8.2 PRIMARY KEY（主键约束） ​

基本定义 ​

主键的隐含规则 ​

添加和删除主键 ​

主键与 InnoDB 聚簇索引 ​

8.3 FOREIGN KEY（外键约束） ​

基本概念 ​

级联操作：ON DELETE 和 ON UPDATE ​

级联操作示例 ​

外键的要求与限制 ​

查看约束名称和删除外键 ​

外键的性能影响 ​

何时不使用外键 ​

8.4 UNIQUE 约束 ​

UNIQUE 允许空值（且允许多个 NULL！） ​

UNIQUE vs PRIMARY KEY ​

8.5 NOT NULL 与 DEFAULT 约束 ​

NOT NULL ​

DEFAULT ​

8.6 CHECK 约束（MySQL 8.0+ 的重大更新） ​

CHECK 约束语法 ​

MySQL 5.7 的 CHECK 陷阱 ​

CHECK 约束的实际应用 ​

8.7 查看约束 ​

方式一：SHOW CREATE TABLE ​

方式二：information_schema ​

8.8 禁用和启用外键检查 ​

8.9 约束命名规范建议 ​

常见错误 ​

错误 1：在 MySQL 5.7 中使用 CHECK 约束以为它有效 ​

错误 2：删除父表记录时未考虑级联后果 ​

错误 3：UNIQUE 和 PRIMARY KEY 混淆 ​

错误 4：外键列数据类型不匹配导致 ALTER TABLE 失败 ​

错误 5：忘记给外键列建索引 ​

本章练习 ​