炫酷的企业网站,建设银行如何设置网站查询密码,浏览器免费大全,国外开网站怎样做平帐前言#xff1a; MySQL的外键简介#xff1a;在 MySQL 中#xff0c;外键 (Foreign Key) 用于建立和强制表之间的关联#xff0c;确保数据的一致性和完整性。外键的作用主要是限制和维护引用完整性 (Referential Integrity)。 主要体现在引用操作发生变化时的处理方式… 前言 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的值变为NULLorder_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 存储引擎。索引要求: 外键列和被引用列都必须建立索引通常是主键或唯一键。规划数据关系: 在设计时需明确父表与子表之间的关系和操作逻辑避免误操作。性能考虑: 在高并发或大规模数据操作时外键可能影响性能需谨慎权衡。 结束语业务开发时根据需求使用或者去除掉外键使用外键时可以参考本篇的介绍建立适合业务场景的外键。 ——如果觉得文章有用点个赞支持一下——
