MySQL中外键的使用及外键约束策略

一、外键约束的概念

外键约束（FOREIGN KEY,缩写FK是数据库设计的一个概念，它确保在两个表之间的关系保持数据的一致性和完整性。

外键是指表中的某个字段的依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束，被依赖的数据表称为主表（父表），设置外键约束的表称为子表或者从表。

比方说：现在有两张表，学生表和班级表，学生表中的班级号字段的值取决于班级表中班级号字段。
在这里插入图片描述

主表（父表）：班级表-班级编号-主键
从表（子表）：学生表-班级编号-外键

二、不使用外键约束会出现的问题

首先创建这两张表，学生表和班级表，然后不添加班级号的外键约束,并且插入一些初始化数据。

（一）创建表及初始化数据

-- 创建班级表
create table t_class(
	cno int(4) primary key auto_increment, -- 班级号自增主键
	cname varchar(12) not null -- 班级名称不为空值
);

-- 查看班级表
select * from t_class;

-- 插入班级信息
insert into t_class values (null,'java01班') ,(null,'python01班'),(null,'大数据01班');


-- 创建学生表（不含有外键约束的）
create table t_student(
	sno int(4) primary key auto_increment, -- 学号为主键自增
	sname varchar(5) not null , -- 姓名为非空约束
	age int(3) check (age >= 18 and age <= 55), -- 年龄为18-55之间 检查约束
	sex char(1) default '男' check (sex = '男' || sex = '女'), -- 性别默认为男，只能是男或者女
	cno int(4) 
);

-- 查看学生表
select * from t_student;

-- 插入学生信息  编号从1001开始
insert into t_student values (1001,'张三',21,'男',1); 
insert into t_student values (null,'李四',21,'男',1); 
insert into t_student values (null,'王五',21,'男',2);
insert into t_student values (null,'赵六',21,'男',3);
insert into t_student values (null,'崔七',21,'男',4); # 插入不存在的班级编号依旧成功
delete from t_student where cno = 4; -- 删除掉错误的数据

在没有外键约束的情况下，其实学生表（从表）中的班级号是可以任意插入的，造成了数据的不一致性

（二）更新和删除表数据

  1. 更新java01班的班级号为9，学生表中的数据并没有进行更新，数据再次不一致
-- 更新班级表数据
-- 将java01班的编号修改为9号
update t_class set cno = 9 where cname = 'java01班' and cno = 1;
select * from t_class; -- 班级表中成功修改
select * from t_student; -- 学生表中java01班的学生的班级编号并没有修改为9
  1. 删除java01班的班级信息，学生表中的java01班的学生信息依旧没有变化，数据不一致性
-- 删除表数据
-- 删除java01班的班级信息
delete from t_class where cno = 9 ;
select * from t_class;
select * from t_student;

（三）小结

在不使用外键约束的情况下，增删改均会影响数据的不一致性和完整性。

三、使用外键约束及外键策略

（一）创建表及初始化数据

-- 创建班级表
create table t_class(
	cno int(4) primary key auto_increment,
	cname varchar(12) not null
);

-- 查看班级表
select * from t_class;

-- 插入班级信息
insert into t_class values (null,'java01班') ,(null,'python01班'),(null,'大数据01班');

-- 创建学生表 外键约束只有表级约束
create table t_student(
	sno int(4) primary key auto_increment, -- 学号为主键自增
	sname varchar(5) not null , -- 姓名为非空约束
	age int(3) check (age >= 18 and age <= 55), -- 年龄为18-55之间 检查约束
	sex char(1) default '男' check (sex = '男' || sex = '女'), -- 性别默认为男，只能是男或者女
	cno int(4),
  constraint fk_stu_classcno	foreign key (cno) references t_class (cno) -- 添加外键约束
);

-- 查看学生表
select * from t_student;

-- 插入学生信息  1.(在没有外键约束的情况下，其实我们插入任何班级号都是可以的)
insert into t_student values (1001,'张三',21,'男',1); -- 这里第一个插入的id会影响后面的id，从1001开始
insert into t_student values (null,'李四',21,'男',1); 
insert into t_student values (null,'王五',21,'男',2);
insert into t_student values (null,'赵六',21,'男',3);
-- > 1452 - Cannot add or update a child row: a foreign key constraint fails (`mytestdb`.`t_student`, CONSTRAINT `fk_stu_classcno` FOREIGN KEY (`cno`) REFERENCES `t_class` 
insert into t_student values (null,'崔七',21,'男',4); # 插入不存在的班级编号报错

这里插入不存在的班级编号时，直接保存，因为外键约束帮我们做出了限制.

1452 - Cannot add or update a child row: a foreign key constraint fails (mytestdb.t_student, CONSTRAINT fk_stu_classcno FOREIGN KEY (cno) REFERENCES t_class

（二） 更新和删除数据

更新和删除数据时，涉及到外键策略，我们先尝试着去改动和删除一些数据。

  1. 更新java01班的班级号为9
update t_class set cno = 9 where cno = 1;

报错：update t_class set cno = 9 where cno = 1
1451 - Cannot delete or update a parent row: a foreign key constraint fails (mytestdb.t_student, CONSTRAINT fk_stu_classcno FOREIGN KEY (cno) REFERENCES t_class (cno))
时间: 0.002s

因为添加了外键约束，两个表已经建立了关系，为了维持数据的一致性，当改动班级表中的班级号时，原有的学生表中的编号也需要改动，默认情况下是不执行的，需要添加一些条件。

  1. 删除java01班的班级信息
delete from t_class where cno = 1;

报错：delete from t_class where cno = 1
1451 - Cannot delete or update a parent row: a foreign key constraint fails (mytestdb.t_student, CONSTRAINT fk_stu_classcno FOREIGN KEY (cno) REFERENCES t_class (cno))
时间: 0.008s
和更新时的报错原因一样，同样是因为外键的原因。

（三）外键策略

tips:cascade 操作 和 set null 操作 在表的创建添加外键约束时即可添加，我这里是为了方便直接修改外键约束策略了。灵活的根据业务将set null 和 cascade 结合起来使用

  1. no action 不允许操作–默认的外键策略 （修改从表的数据为Null，然后再修改主表的数据） 这种方法比较傻，就是硬写SQL
-- 修改java01班的班级编号为9
-- 1. 先修改学生表中java01班的数据为null
update t_student set cno = null where cno = 1;
-- 2. 更新班级表中的数据
update t_class set cno = 9 where cno = 1;
-- 3. 更新学生表中的数据
update t_student set cno = 9 where sno in (1001,1002);
  1. cascade 级联操作，操作主表的时候影响从表的外键信息 （先删除之前的外键约束再重新添加外键约束）。
-- 1.删除原有外键约束
alter table t_student drop foreign key fk_stu_classcno;
-- 2.添加新的外键约束策略  --- 在更新和删除时进行级联操作
alter table t_student add constraint fk_stu_classcno foreign key (cno) references t_class (cno) on update cascade on delete cascade;
-- 3. 更新数据 将java01班的班级号换为 19
update t_class set cno = 19 where cno = 9;
-- 4. 查询验证
select * from t_class;
select * from t_student;
  1. set null 置空操作，操作主表的时候影响从表的外键信息，从表对应的值为null值（先删除之前的外键约束然后重新添加新的外键约束）。
-- 1.删除原有外键约束
alter table t_student drop foreign key fk_stu_classcno;
-- 2.添加新的外键约束策略 --- 更新和删除时将从表的外键值都置为null
alter table t_student add constraint fk_stu_classcno foreign key (cno) references t_class (cno) on update set null  on delete set null;
-- 3. 更新数据 将java01班的班级号换为 29
update t_class set cno = 29 where cno = 19;
-- 4. 手动更新学生从表的数据
update t_student set cno = 29 where sno in (1001,1002);
-- 4. 查询验证
select * from t_class;
select * from t_student;

下面是一个在创建表时，添加外键约束策略的例子：

-- 创建班级表
create table t_class(
	cno int(4) primary key auto_increment,
	cname varchar(12) not null
);

-- 插入班级信息
insert into t_class values (null,'java01班') ,(null,'python01班'),(null,'大数据01班');

-- 查看班级表
select * from t_class;

-- 创建学生表 外键约束只有表级约束
create table t_student(
	sno int(4) primary key auto_increment, -- 学号为主键自增
	sname varchar(5) not null , -- 姓名为非空约束
	age int(3) check (age >= 18 and age <= 55), -- 年龄为18-55之间 检查约束
	sex char(1) default '男' check (sex = '男' || sex = '女'), -- 性别默认为男，只能是男或者女
	cno int(4),
	-- 当主表中的数据更新时，从表数据级联更新，当主表数据删除时，从表数据设置为Null
  constraint fk_stu_classcno	foreign key (cno) references t_class (cno) on update cascade on delete set null
);

-- 插入学生信息  1.(在没有外键约束的情况下，其实我们插入任何班级号都是可以的)
insert into t_student values (1001,'张三',21,'男',1); -- 这里第一个插入的id会影响后面的id，从1001开始
insert into t_student values (null,'李四',21,'男',1); 
insert into t_student values (null,'王五',21,'男',2);
insert into t_student values (null,'赵六',21,'男',3);

-- 查看学生表
select * from t_student;

-- 更新java01班班级编号为9
update t_class set cno = 9 where cno = 1;
select * from t_class;
select * from t_student;

-- 删除大数据01班
delete from t_class where cno = 3;
select * from t_class;
select * from t_student;

四、总结

当使用外键约束时，需要注意以下几点：

  • 外键约束只有表级约束，没有列级约束。
  • 外键约束会影响表的性能，因为数据库必须对每个写操作执行额外的检查。
  • 如果尝试插入不符合外键约束的行，数据库会抛出一个错误。
  • 根据不同的业务需求自定义不同的外键策略 （cascade || set null）“constraint fk_stu_classcno foreign key (cno) references t_class (cno) on update cascade on delete set null”

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如若转载，请注明出处：/a/135981.html

如若内容造成侵权/违法违规/事实不符，请联系我们进行投诉反馈qq邮箱809451989@qq.com，一经查实，立即删除！

相关文章

[vuex] unknown mutation type: SET_SOURCE

[vuex] unknown mutation type: SET_SOURCE

项目中使用了vuex&#xff0c;并且以模块的形式分好之后。在调用的时候出现了以上问题 /*当我们commit的时候要注意要加上模块的名字 user是模块名称&#xff0c;SET_SOURCE是user模块中定义的方法 正确写法&#xff1a;*/ this.$store.commit("user/SET_SOURCE", th…
阅读更多...
linux之IPC

linux之IPC

linux之IPC 什么是IPC共享内存(shm)ftokshmgetshmatshmdtshmctl 消息队列msggetmsgrcvmsgsndmsgctl 旗语(信号量)semgetsemctlsemopsem三级标题三级标题 ipc命令守护进程查看守护进程 什么是IPC IPC: Inter(内核) Process(进程) Communicton&#xff08;通信&#xff09; 共享内…
阅读更多...
解决wrong fs type, bad option, bad superblock on /dev/sda1问题

解决wrong fs type, bad option, bad superblock on /dev/sda1问题

1 背景 某天挂载硬盘的时候&#xff0c;系统提示了如下错误&#xff1a; 在此记录排查过程以及解决方案。 2 排查过程 出现这种问题应该先尝试从日志入手&#xff0c;输入&#xff1a; sudo dmesg | tail输出如下&#xff1a; 关键信息&#xff1a; [ 164.750178] ntfs3:…
阅读更多...
基于SSM的微博网站的设计与实现

基于SSM的微博网站的设计与实现

末尾获取源码 开发语言&#xff1a;Java Java开发工具&#xff1a;JDK1.8 后端框架&#xff1a;SSM 前端&#xff1a;采用JSP技术开发 数据库&#xff1a;MySQL5.7和Navicat管理工具结合 服务器&#xff1a;Tomcat8.5 开发软件&#xff1a;IDEA / Eclipse 是否Maven项目&#x…
阅读更多...
Runway 最强竞品 Pika 1.0 预告来袭！文生视频效果堪比迪士尼动画！重新定义动画生成新范式！

Runway 最强竞品 Pika 1.0 预告来袭！文生视频效果堪比迪士尼动画！重新定义动画生成新范式！

作者 | 张雨霏、王二狗 Runway是AI生成视频赛道的绝对霸主吗&#xff1f; 不一定&#xff01; 就在这两天天&#xff0c;Pika在推特上官宣——Pika 1.0即将来袭&#xff01; 网友看到后都直呼 Amazing &#x1f929;&#xff01;Unexpected! &#x1f525; 还有网友表示未来…
阅读更多...
Day58_《MySQL索引与性能优化》

Day58_《MySQL索引与性能优化》

文章目录 一、SQL执行顺序二、索引简介1、关于索引2、索引的类型Btree 索引Btree 索引 三、Explain简介四、Explain 详解1、id2、select_type3、table4、type5、possible_keys6、key7、key_len8、ref9、rows10、Extra11、小案例 五、索引优化1、单表索引优化2、两表索引优化3、…
阅读更多...
Spring Boot 整合xxl-job实现分布式定时任务

Spring Boot 整合xxl-job实现分布式定时任务

xxl-job介绍 XXL-JOB是一个分布式任务调度平台&#xff0c;其核心设计目标是开发迅速、学习简单、轻量级、易扩展。现已开放源代码并接入多家公司线上产品线&#xff0c;开箱即用。 xxl是xxl-job的开发者大众点评的许雪里名称的拼音开头。 设计思想 将调度行为抽象形成“调度…
阅读更多...
CDN加速与网站服务器优化提速的区别

CDN加速与网站服务器优化提速的区别

在当今数字化时代&#xff0c;网站性能成为了业务成功的关键因素之一。为了提升用户体验和页面加载速度&#xff0c;许多网站采取了不同的优化手段&#xff0c;其中CDN加速和网站服务器优化提速是两个重要的方向。然而&#xff0c;它们在实现高效网站性能方面有着不同的角色和功…
阅读更多...
【86 backtrader实现crypto交易策略】backtrader和ccxt对接实现中低频自动化交易-01

【86 backtrader实现crypto交易策略】backtrader和ccxt对接实现中低频自动化交易-01

最近有点空闲，尝试把backtrader和一些实盘交易的接口对接一下，方便大家进行中低频交易，主要目标包括：股票(qmt),期货(ctpbee), crypto(ccxt),外盘交易(ib，已实现，但是版本比较旧，后期会继续更新). 这个周末尝试实现了backtrader和ccxt的对接，主要是参考了下面的开源代…
阅读更多...
k8s 1.28.3 使用containerd

k8s 1.28.3 使用containerd

文章目录 环境说明最终结果环境配置时钟同步 主机名称配置主机名解析关闭swap安装ipvs 安装containerd安装containerd生成配置修改配置开启containerd服务 安装runc安装k8s安装kubelet kubeadm kubectl获取kubernetes 1.28组件容器镜像 拉取镜像初始化集群方法一&#xff08;不…
阅读更多...
一文读懂微前端

一文读懂微前端

1 语雀文档 https://www.yuque.com/chanwj/vlkwxk/qvpv3kqws5hno3qt?singleDoc# 《微前端》本文使用的参考文档均以链接方式粘贴于文章内&#xff0c;十分感谢~ 2 项目github链接 如果你觉得本文档对你有用&#xff0c;恳请github仓库给个star~https://github.com/OmegaCh…
阅读更多...
231112-中文错别字识别与纠正问题的大模型与小模型调研

231112-中文错别字识别与纠正问题的大模型与小模型调研

A. 引言 当前&#xff0c;以ChatGPT为代表的大语言模型&#xff08;Large Language Models, LLMs&#xff09;正引领着新一轮工业革命。ChatGPT最开始的研究领域隶属于NLP的一个子问题&#xff0c;其输入是text&#xff0c;输出也是text。在从文本输入到文本输出的诸多应用场景…
阅读更多...
No177.精选前端面试题，享受每天的挑战和学习

No177.精选前端面试题，享受每天的挑战和学习

🤍 前端开发工程师（主业）、技术博主（副业）、已过CET6 🍨 阿珊和她的猫_CSDN个人主页 🕠 牛客高级专题作者、在牛客打造高质量专栏《前端面试必备》 🍚 蓝桥云课签约作者、已在蓝桥云课上架的前后端实战课程《Vue.js 和 Egg.js 开发企业级健康管理项目》、《带你从入…
阅读更多...
PgSQL技术内幕-Analyze做的那些事-pg_statistic系统表

PgSQL技术内幕-Analyze做的那些事-pg_statistic系统表

PgSQL的优化器为一个查询生成一个执行效率相对较高的物理执行计划树。执行效率的高低依赖于代价估算。比如估算查询返回的记录条数、记录宽度等&#xff0c;就可以计算出IO开销&#xff1b;也可以根据要执行的物理操作估算出CPU代价。那么估算依赖的信息来源哪呢&#xff1f;系…
阅读更多...
代理模式-静态动态代理-jdk动态代理-cglib动态代理

代理模式-静态动态代理-jdk动态代理-cglib动态代理

代理模式 静态代理 动态代理&#xff1a;jdk动态代理 cglib动态代理 注意 &#xff1a;下面的代码截图 要配合文字去看 我对代码的每一步都做了解释 所以需要配合图片观看提取吗1111https://pan.baidu.com/s/1OxQSwbQ--t5Zvmwzjh1T0A?pwd1111 这里直接把项目文件 及代码 …
阅读更多...
【Seata源码学习 】 AT模式 第一阶段 @GlobalTransaction的扫描

【Seata源码学习 】 AT模式 第一阶段 @GlobalTransaction的扫描

1. SeataAutoConfiguration 自动配置类的加载 基于SpringBoot的starter机制&#xff0c;在应用上下文启动时&#xff0c;会加载SeataAutoConfiguration自动配置类 # Auto Configure org.springframework.boot.autoconfigure.EnableAutoConfigurationio.seata.spring.boot.aut…
阅读更多...
外中断的应用

外中断的应用

前言 软件基础操作参考这篇博客&#xff1a; LED数码管的静态显示与动态显示&#xff08;KeilProteus&#xff09;-CSDN博客https://blog.csdn.net/weixin_64066303/article/details/134101256?spm1001.2014.3001.5501实验一&#xff1a;P1口上接8个LED灯&#xff0c;在外部…
阅读更多...
cesium如何实现区域下钻

cesium如何实现区域下钻

首先&#xff0c;这里讲一下数据源&#xff0c;数据源是拷贝的DataV.GroAtlas里的数据&#xff0c;这里整合了一下之前发的区域高亮的代码来实现的&#xff0c;单击左键使得区域高亮&#xff0c;每次点击都移除上一次点击的模块&#xff0c;双击左键&#xff0c;实现区域下钻并…
阅读更多...
C++ 对象的拷贝、赋值、清理和移动（MyString类）

C++ 对象的拷贝、赋值、清理和移动（MyString类）

MyString类 MyString.h #ifndef MYSTRING_H #define MYSTRING_H#include <iostream> using namespace std;class MyString {private:char* str nullptr;unsigned int MaxSize 0;public:MyString(); /*默认构造函数*/MyString(unsigned int n); /*有…
阅读更多...
SpringBoot系列-2 自动装配

SpringBoot系列-2 自动装配

背景&#xff1a; Spring提供了IOC机制&#xff0c;基于此我们可以通过XML或者注解配置&#xff0c;将三方件注册到IOC中。问题是每个三方件都需要经过手动导入依赖、配置属性、注册IOC&#xff0c;比较繁琐。 基于"约定优于配置"原则的自动装配机制为该问题提供了一…
阅读更多...
最新文章

Copyright @ 2022~2023