四、MySQL约束

​一、基本概念

定义

约束是数据库中用于限制表数据的规则，通过约束可以强制保证数据的正确性、有效性和完整性，防止非法数据进入数据库。

作用

保证数据完整性：确保数据符合业务逻辑（如主键唯一标识、外键关联一致性）；
防止错误数据：限制字段值范围（如非空、唯一、检查约束），避免无效数据（如空值、重复值）；
维护数据一致性：通过外键约束保证表与表之间的关联关系（如子表的外键必须对应父表的主键）。

二、约束的分类

根据约束的功能，可分为6类，对应不同的关键字：

三、外键约束的删除更新行为

外键约束的删除/更新行为（ON DELETE/ON UPDATE）用于定义父表记录变更时，子表对应外键记录的处理方式。常见行为如下：

1、NO ACTION（或RESTRICT）

说明：当父表删除/更新记录时，先检查子表是否有对应外键。若有，则不允许删除/更新父表记录（阻止操作）。
区别：NO ACTION是SQL标准行为，RESTRICT是MySQL的别名，两者完全一致（MySQL中无实际区别）。

2、CASCADE（级联）

说明：当父表删除/更新记录时，自动删除/更新子表中对应的外键记录。
示例：父表parent的主键id=1被删除，子表child中外键parent_id=1的记录会被自动删除；若父表id=1更新为id=2，子表parent_id=1会被自动更新为2。

3、SET NULL（设为空）

说明：当父表删除记录时，自动将子表中对应的外键值设为null（要求外键字段允许null，否则报错）。
注意：SET NULL仅适用于删除操作（更新操作不支持，因为更新父表主键时，子表外键应改为新值，而非null）。

4、SET DEFAULT（设为默认值）

说明：当父表变更时，子表外键列设为默认值（如DEFAULT 0）。
限制：MySQL的InnoDB引擎不支持此行为（仅MyISAM支持，但MyISAM无外键约束，故实际无意义）。

四、约束管理

1、查看现有约束

在修改之前，你必须先知道表里有什么约束，以及它们叫什么名字（特别是外键和索引名）。
命令： SHOW CREATE TABLE 表名;
场景： 你接手了一个老项目，想知道 orders 表里的外键到底叫什么名字，或者想确认某个字段有没有加唯一索引。

— 查看 orders 表的完整建表语句，包含所有约束定义
SHOW CREATE TABLE orders;

输出结果中，CONSTRAINT fk_xxx FOREIGN KEY… 这一行里的 fk_xxx 就是外键的逻辑名称，删除时必须用它。而 UNIQUE KEY uk_xxx 里的 uk_xxx 是唯一索引的名称。

2、添加新约束

当业务规则变严格时（例如：以前允许邮箱为空，现在必须必填且唯一），我们需要添加约束。
添加非空/默认/检查约束
这类约束直接依附于字段，使用 MODIFY 修改字段定义即可。

— 给 email 字段添加非空约束和默认值
ALTER TABLE users
MODIFY COLUMN email VARCHAR(100) NOT NULL DEFAULT 'unknown@example.com';

添加主键、唯一、外键约束
这类约束通常作为表级对象存在。

— 1. 添加唯一约束 (假设之前没加)
ALTER TABLE users ADD UNIQUE (phone_number);

— 2. 添加外键约束 (最常用)
ALTER TABLE orders
ADD CONSTRAINT fk_order_user
FOREIGN KEY (user_id) REFERENCES users(id);

ADD CONSTRAINT 后面的名字（如 fk_order_user）是你自己起的，建议遵循 fk_子表_父表 的命名规范，方便日后维护。

3、移除约束

当业务逻辑变更，或者为了优化写入性能（高并发系统常去掉数据库外键），需要移除约束。
删除非空/默认约束
同样通过 MODIFY 字段定义来“覆盖”旧规则。

— 去掉 status 字段的非空约束（允许为空）
ALTER TABLE users
MODIFY COLUMN status TINYINT NULL;

删除主键、唯一、外键约束
这里要注意语法的区别：
删除主键： 一个表只有一个主键，不需要名字。

ALTER TABLE users DROP PRIMARY KEY;

删除唯一约束： 实际上删的是对应的索引。

— 这里的 uk_phone 是索引名，不是字段名
ALTER TABLE users DROP INDEX uk_phone;

删除外键约束： 必须指定外键名称。

ALTER TABLE orders DROP FOREIGN KEY fk_order_user;

删除外键后，如果该外键字段上自动生成的索引也不再需要，记得手动 DROP INDEX 把它也删掉，否则它会继续占用空间并影响写入速度。

4、修改约束

MySQL 没有 ALTER CONSTRAINT 语法。“改” = “先删后加”。
场景举例：
原本订单表的外键策略是“禁止删除用户（RESTRICT）”，现在业务变更为“删除用户时，将其订单归属置空（SET NULL）”。

— 第一步：查名字（如果不确定）
SHOW CREATE TABLE orders;
— 假设查到外键名叫 fk_order_user

— 第二步：删掉旧的外键约束
ALTER TABLE orders DROP FOREIGN KEY fk_order_user;

— 第三步：加上新的外键约束（带新的级联策略）
ALTER TABLE orders
ADD CONSTRAINT fk_order_user_new — 建议换个新名字，或者沿用旧名
FOREIGN KEY (user_id) REFERENCES users(id)
ON DELETE SET NULL;

五、约束设计最佳实践

• 主键选择：优先使用无业务含义的自增整数（如 AUTO_INCREMENT），避免因业务变化而修改主键。
• 外键命名：使用 fk_子表_父表 的命名规范，便于理解和维护。
• 检查约束：在 MySQL 8.0.16 及以上版本中积极使用，将数据验证逻辑下沉到数据库层。
• 性能考量：外键约束会带来一定的性能开销，在写入频繁的超大型表中需谨慎评估。但通常其带来的数据一致性保障远大于性能损失。
• 业务匹配：根据业务逻辑仔细选择 ON DELETE 和 ON UPDATE 行为，例如：
  • 核心业务数据关联（如订单-用户）使用 RESTRICT 或 CASCADE。
  • 日志、历史记录等可独立存在的数据关联可使用 SET NULL。

六、综合示例

场景背景：电商系统的“用户”与“订单”
假设我们正在为一个电商平台设计数据库。这里有两个核心角色：
用户表 (users)：也就是“父表”。
订单表 (orders)：也就是“子表”。
业务逻辑是：一个用户可以下多个订单，但一个订单必须属于某一个用户。如果用户注销了，他的订单该怎么处理？这就是我们要通过约束来解决的问题。

1、建表实战（综合应用约束）

请看下面的 SQL 代码，我会在代码注释中为你拆解每一个约束的作用：

— 1. 创建父表：用户表
CREATE TABLE users (
user_id INT PRIMARY KEY, — 【主键约束】：用户的唯一身份证，非空且唯一
username VARCHAR(50) NOT NULL, — 【非空约束】：注册时必须填名字，不能留空
email VARCHAR(100) UNIQUE, — 【唯一约束】：邮箱不能重复，防止两人用同一个邮箱注册
status TINYINT DEFAULT 1 — 【默认约束】：如果不填状态，默认为1（代表“正常”）
);

— 2. 创建子表：订单表
CREATE TABLE orders (
order_id INT PRIMARY KEY, — 【主键约束】：订单号唯一
user_id INT, — 这是一个普通字段，准备用来做外键
amount DECIMAL(10, 2) CHECK (amount > 0), — 【检查约束】：订单金额必须大于0，防止录入负数金额
create_time DATETIME,

— 【外键约束】核心登场！
CONSTRAINT fk_user_order — 给这个外键关系起个名字，方便管理
FOREIGN KEY (user_id) — 子表的哪个字段是外键？是 user_id
REFERENCES users(user_id) — 它关联的是父表(users)的哪个字段？是 user_id
ON DELETE CASCADE — 【删除行为】：父表删人，子表订单跟着删（级联删除）
ON UPDATE CASCADE — 【更新行为】：父表改ID，子表订单跟着改（级联更新）
);

2、深度解析：外键的删除与更新行为

在这个例子中，我们在 orders 表设置了 ON DELETE CASCADE 和 ON UPDATE CASCADE。让我们看看在实际操作中会发生什么神奇的事情。

场景 A：正常的插入（检查约束与非空约束）

— 插入一个正常用户
INSERT INTO users (user_id, username, email) VALUES (1, '张三', 'zhang@test.com');

— 插入一个订单，金额为 100
INSERT INTO orders (order_id, user_id, amount) VALUES (1001, 1, 100.00);
— 成功！因为用户1存在，且金额大于0。

— 尝试插入一个金额为 -50 的订单
INSERT INTO orders (order_id, user_id, amount) VALUES (1002, 1, –50.00);
— 报错！违反了 CHECK (amount > 0) 检查约束。

场景 B：外键的“连坐”机制（CASCADE）

这是外键最强大的地方。注意看我们设置的 ON DELETE CASCADE（级联删除）。
操作： 管理员决定封禁并删除“张三”这个用户。

DELETE FROM users WHERE user_id = 1;

结果：

• users 表中 user_id = 1 的记录被删除了。
• 关键点来了：MySQL 会自动去 orders 表里找，发现订单 1001 是属于用户 1 的。
• 因为设置了 CASCADE，订单 1001 也会被自动删除！

场景 C：如果想保留订单怎么办？（SET NULL）

如果在实际业务中，即便用户注销了，我们也想保留他的订单记录用于财务审计，我们就不能用 CASCADE 了，而应该在建表时使用 ON DELETE SET NULL。
假设我们修改了外键策略为 SET NULL：
操作： 再次删除用户 1。

DELETE FROM users WHERE user_id = 1;

结果：

• users 表中用户 1 没了。
• orders 表中，订单 1001 依然存在！
• 但是，订单 1001 的 user_id 字段变成了 NULL。
• 注意：如果要使用 SET NULL，你的子表外键字段（user_id）必须允许为 NULL（即建表时不能加 NOT NULL）。