MySQL外键类型与应用场景总结

冯奎
2025-01-11 18:15:25
MySQL
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	强制要求父表记录的删除或更新必须先解除子表关联。	增加了操作复杂性，但能严格保护数据完整性。