MySQL数据库如何实现多表间指定字段数据的自动删除和自动更新？

本文链接：https://blog.csdn.net/weixin_56684524/article/details/140342663

场景

我把学生信息存在 Student表里，班级信息存在 Class表里。Student表的每一位学生都有 Class表里对应的班级号cno 。
可当我 Class表中班级号 393班被改为 387班时，我就必须要去 Student 表中把原来393班所有同学的班级号都改为387班。
可是这个过程我得写两条sql语句。能不能就执行一条sql语句，实现上面这个过程的更新操作呢？

问题

如何实现只执行一条SQL语句，同时修改不同表对应相同字段的数据值呢？

解决

可以利用数据库的 级联删除 / 级联更新 操作实现自动修改多张表的同一字段值。
注：如果你已经创建了表且有数据的情况下，想实现级联自动更新或删除，那么这需要你重新设计表结构，再重新创建表。因此，我们这个工作应该在数据库表设计的阶段完成。如果有数据的也不要紧，先备份一下数据，等新表创建后再插入也不迟。

我们以修改班级号为例来分情况实现级联删除或级联更新 ”

1、我们先来看看 班级表 Class 和学生表 STUDENT

	班级表 Class{
	 	cno 班级号（主键）,
		...
	 }
	 
	学生表 STUDENT {              
		sid 学号（主键）,                      
		cno 班级号（外部主键 简称 外键 ),
		...                
	}

假设以下情况发生时，我们该如何如何创建表，实现对应操作。

例1：当 cno=393 的班级号改变为 cno=387 的班级号时，393 班所有学生的班级号也应该 随着班级表中的班级号的更新而更新 。

	create table student(
	
		Sid int not null primary key auto_increment,
		
		Cno varchar(10) not null ,
		
		...
		
		FOREIGN KEY(Cno) REFERENCES Class(Cno) ON UPDATE CASCADE
	);

例2：当 cno=387 的班级号被删除时，而该班还有学生，不能造成学生没班级了，所以拒绝该操作。

create table student(
	
	Sid int not null primary key auto_increment,
		
	Cno varchar(10) not null ,
		
	...
			
	FOREIGN KEY(Cno) REFERENCES Class(Cno) ON DELETE NO ACTION
);

2、我们再来看另外一个案例：
表结构如下：

菜品表 goods { 
	gId 菜品号(主键)
	gName 菜品名
	…
}
		
图片表 img {               
	iId 图片号 (主键)           
	gId 菜品id (外键)                 
	…                             
}

例3：当菜品表中 gId=3 的菜品被删除时，
图片表中菜品id 为3的图片也应该 随着菜品表中 gId=3 这道菜的删除而删除 。

create table img(
	
	iId int not null primary key ,
		
	gId int not null ,
	
	…
	
	FOREIGN KEY(gId) REFERENCES goods(gId) ON DELETE CASCADE
);

总结

```
  FOREIGN KEY(abc) REFERENCES table2(abc) ON UPDATE CASCADE 
```
执行：当table2 的表字段abc 值被修改时，该表中对应 abc字段相同的值自动修改
```
 FOREIGN KEY(abc) REFERENCES table2(abc) ON DELETE NO ACTION
```
执行：当table2 的表字段abc 值被删除时，拒绝该删除操作，既不删除table2的数据，也不删除该表中的数据
```
 FOREIGN KEY(abc) REFERENCES table2(abc) ON DELETE CASCADE
```
执行：当table2 的表字段abc 被删除时，该表中abc字段相同值对应的数据自动删除

注：

参照的外部键必须是主键
FOREIGN KEY(外键字段名) 关键字中：所填的 外键字段名 必须是参考表的 主键。
建表顺序：
若 Student 表中建立了参考 Class 表 的外键 cno ，那么建表的顺序必须是：先建 Class 表 再建 Student 表。因为没有Class表的cno, Stundent 表去哪找Class表来参考呢？