MySQL外键类型与应用场景总结：优缺点一目了然

前言：

MySQL的外键简介：在 MySQL 中，外键 (Foreign Key) 用于建立和强制表之间的关联，确保数据的一致性和完整性。外键的作用主要是限制和维护引用完整性 (Referential Integrity)。

• 主要体现在引用操作发生变化时的处理方式（即 ON DELETE 和 ON UPDATE 的行为）。
• 外键类型一共有四种： RESTRICT、 CASCADE 、 SET NULL 、 NO ACTION。接下来通过测试来演示各自的作用效果。

1、 外键效果演示

1.1、创建添加两张表数据

-- 创建父表
CREATE TABLE `users` (`user_id` INT NOT NULL AUTO_INCREMENT,`username` VARCHAR(255) NOT NULL,PRIMARY KEY (`user_id`)
);-- 创建子表
CREATE TABLE `orders` (`order_id` INT NOT NULL AUTO_INCREMENT,`order_date` DATE NOT NULL,`user_id` INT,PRIMARY KEY (`order_id`)
);-- 插入父表数据
INSERT INTO `users` (`username`) VALUES ('Alice');
INSERT INTO `users` (`username`) VALUES ('Bob');-- 插入子表数据
INSERT INTO `orders` (`order_date`, `user_id`) VALUES ('2024-12-25', 1);
INSERT INTO `orders` (`order_date`, `user_id`) VALUES ('2024-12-26', 2);

---

1.2、测试外键作用效果

1.2.1、RESTRICT

• 创建RESTRICT外键

-- 添加外键约束到现有的子表 `orders`
ALTER TABLE `orders`
ADD CONSTRAINT `fk_user_id`
FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

• 主表 删除和更新 已在 子表的外键中已存在 的数据

-- 删除已被引用的外键
DELETE FROM `users` WHERE `user_id` = 1-- 输出结果 -- 
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)
> 查询时间: 0.013s-- 修改已被引用的外键
UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 1-- 输出结果 -- 
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`) ON DELETE RESTRICT ON UPDATE RESTRICT)
> 查询时间: 0.009s

• 查看子表变化

– 因为删除和更新都执行失败，所以子表没有变化。
总结：RESTRICT类型的外键，如果该记录在子表中有引用，禁止删除或更新父表中的记录。

---

1.2.2、CASCADE

• 创建CASCADE外键

-- 添加外键约束到 `orders` 表，使用 CASCADE
ALTER TABLE `orders`
ADD CONSTRAINT `fk_user_id`
FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE CASCADE
ON UPDATE CASCADE;

• 主表 删除和更新 已在 子表的外键中已存在 的数据

-- 删除已被引用的外键
DELETE FROM `users` WHERE `user_id` = 1-- 输出结果 -- 
> Affected rows: 1
> 查询时间: 0.016s-- 修改已被引用的外键
UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 2-- 输出结果 -- 
> Affected rows: 1
> 查询时间: 0.013s

• 查看子表变化

– 因为两条SQL都执行成功。order_id = 1的数据被删除，order_id = 2的user_id的值被修改为3
总结：CASCADE类型的外键，当父表中的记录被删除或更新时，子表中的相关记录也会自动被删除或更新。

---

1.2.3、SET NULL

• 创建SET NULL外键

-- 确保子表的外键列允许 NULL
ALTER TABLE `orders`
MODIFY COLUMN `user_id` INT NULL;-- 添加外键约束到 `orders` 表，使用 SET NULL
ALTER TABLE `orders`
ADD CONSTRAINT `fk_user_id`
FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE SET NULL
ON UPDATE SET NULL;

• 主表 删除和更新 已在 子表的外键中已存在 的数据

-- 删除已被引用的外键
DELETE FROM `users` WHERE `user_id` = 1-- 输出结果 -- 
> Affected rows: 1
> 查询时间: 0.014s-- 修改已被引用的外键
UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 2-- 输出结果 -- 
> Affected rows: 1
> 查询时间: 0.012s

• 查看子表变化

– 两条SQL都执行成功。order_id = 1的user_id的值变为NULL，order_id = 2的user_id的值变为NULL，
总结：SET NULL类型的外键，当父表记录被删除或更新时，子表中对应的外键值会更新为 NULL。

---

1.2.4、NO ACTION

• 创建NO ACTION外键

-- 添加外键约束，使用 NO ACTION
ALTER TABLE `orders`
ADD CONSTRAINT `fk_user_id`
FOREIGN KEY (`user_id`)
REFERENCES `users`(`user_id`)
ON DELETE NO ACTION
ON UPDATE NO ACTION;

• 主表 删除和更新 已在 子表的外键中已存在 的数据

-- 删除已被引用的外键
DELETE FROM `users` WHERE `user_id` = 1-- 输出结果 -- 
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))
> 查询时间: 0.013s-- 修改已被引用的外键
UPDATE `users` SET `user_id` = 3 WHERE `user_id` = 2-- 输出结果 -- 
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`test`.`orders`, CONSTRAINT `fk_user_id` FOREIGN KEY (`user_id`) REFERENCES `users` (`user_id`))
> 查询时间: 0.025s

• 查看子表变化

– 因为删除和更新都执行失败，所以子表没有变化。
总结：NO ACTION类型的外键（和RESTRICT的作用相同），如果该记录在子表中有引用，禁止删除或更新父表中的记录。

---

1.3、外键作用描述以及优缺点总结

1.3.1、RESTRICT

• 描述：即父表记录在被子表引用时，无法被删除或更新。
• 适用场景：适合需要严格控制父表记录操作的场景。
• 优点：防止意外的数据丢失。
• 缺点：增加操作复杂性。

1.3.2、CASCADE

• 描述：级联操作。当父表中的记录被删除或更新时，子表中的相关记录也会自动被删除或更新。
• 适用场景：当子表记录与父表记录绑定紧密时，例如订单表和订单明细表。
• 优点：简化了复杂的删除或更新操作，自动维护数据一致性。
• 缺点：操作不当可能导致数据大量丢失或被误修改。

1.3.3、SET NULL

• 描述：当父表记录被删除或更新时，子表中对应的外键值会设置为 NULL。
• 适用场景：当子表的记录在父表记录删除后依然有意义时，外键列必须允许 NULL。
• 优点：保留了子表记录，同时删除或更新父表记录。
• 缺点：如果没有后续维护，可能导致孤立的数据。

1.3.4、NO ACTION（等价于 RESTRICT）

• 描述：禁止删除或更新父表中的记录，如果该记录在子表中有引用。
• 适用场景：强制父表记录必须首先解除子表中的关联。
• 优点：明确控制了数据的删除或更新，防止意外影响子表数据。
• 缺点：操作复杂性增加，要求开发者手动处理关联关系。

---

2、外键类型适用场景总结（表格）

外键类型	适用场景	注意事项
CASCADE	父子关系强关联，父表删除或更新后子表无条件跟随。	谨慎使用，避免误删除或误更新。
SET NULL	子表记录在父表删除或更新后仍有意义，允许外键列为 NULL。	子表的外键列必须允许 NULL，需谨防数据孤立。
NO ACTION / RESTRICT	强制要求父表记录的删除或更新必须先解除子表关联。	增加了操作复杂性，但能严格保护数据完整性。

---

3、外键于业务开发而言的优缺点

3.1、优点

• 数据完整性: 防止孤立记录，确保父表与子表之间的关联关系一致。
• 自动化处理: 配合 CASCADE 或 SET NULL，可以自动处理相关记录，减少手动操作的复杂性。
• 业务约束: 通过外键约束明确表间关系，增强业务逻辑的约束力。

3.2、缺点

• 性能开销: 外键约束会对插入、更新、删除操作产生额外的性能开销，尤其是在大量操作时。
• 操作复杂性: 需要对数据表操作进行规划，增加开发维护成本。
• 限制灵活性: 外键约束的存在可能限制某些业务操作，例如无法随意删除父表记录。

---

4、外键的使用注意事项

• 引擎限制: MySQL 的外键功能仅支持 InnoDB 存储引擎。
• 索引要求: 外键列和被引用列都必须建立索引（通常是主键或唯一键）。
• 规划数据关系: 在设计时需明确父表与子表之间的关系和操作逻辑，避免误操作。
• 性能考虑: 在高并发或大规模数据操作时，外键可能影响性能，需谨慎权衡。

结束语：业务开发时，根据需求使用或者去除掉外键，使用外键时，可以参考本篇的介绍，建立适合业务场景的外键。

——如果觉得文章有用💪，点个赞，支持一下👏！——