数据库外键和级联操作

2021/4/25 19:26:49

本文主要是介绍数据库外键和级联操作,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!

  1. 外键的作用,以下表为例:
    在这里插入图片描述
--创建学生信息表
create table StInfo(
	StuNum varchar(20),
	StuName varchar(20),
	sex varchar(10),
	department varchar(30)
);
--插入学生数据
insert into StInfo values('001','张三','男','计算机系'),('002','李四','男','计算机系'),('003','王五','女','体育系'),('004','铁蛋','女','体育系');
--查询表数据
select* from StInfo;

对于上表,我们发现department列存在数据冗余,如果计算机系改名为计算机部,需要修改多个数据,那怎么解决呢?
2. 解决办法:思路:创建两个表,学生表和系表,如下图,想修改计算机系为计算机部的话,只需要通过建立外键和级联,直接修改depmt系表当中的计算机系为计算机部即可。
(1)步骤一:创建两个表学生表和部门表,为学生表添加外键。
在这里插入图片描述

create table StInfo(
	StuNum varchar(20),
	StuName varchar(20),
	sex varchar(10),
	dep_id 	int,
	--设置外键
	--格式:constraint 外键名 foreign key (本表列名) reference 主表名(主表列名);
	constraint s_d_id foreign key (dep_id) references depmt(id)
);
insert into StInfo values('001','张三','男','1'),('002','李四','男','1'),('003','王五','女','2'),('004','铁蛋','女','2');
select* from StInfo;

在这里插入图片描述

create table depmt(
		id int PRIMARY key,
		department varchar(20)
);
insert into depmt values(1,'计算机系'),(2,'体育系');
select *from depmt;

添加外键后,修改、删除、添加外键列数据有了限制

--比如修改dep_id=3操作报错id,因为只有1和2
update StInfo set dep_id = 3 where dep_id=1;
> 1452 - Cannot add or update a child row: a foreign key constraint fails (`db1`.`stinfo`, CONSTRAINT `s_d_id` FOREIGN KEY (`dep_id`) REFERENCES `depmt` (`id`))
update depmt set id = 3 where id = 1;
> 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`db1`.`stinfo`, CONSTRAINT `s_d_id` FOREIGN KEY (`dep_id`) REFERENCES `depmt` (`id`))
--删除外键关联列,报错
alter table depmt drop id;
> 1025 - Error on rename of '.\db1\#sql-c80_b' to '.\db1\depmt' (errno: 150)
alter table StInfo drop dep_id;
> 1025 - Error on rename of '.\db1\#sql-c80_b' to '.\db1\StInfo' (errno: 150)
--添加数据时,dep_id只能为1或2,否则报错
insert into StInfo values('005','钢蛋','男','3');
> 1452 - Cannot add or update a child row: a foreign key constraint fails (`db1`.`stinfo`, CONSTRAINT `s_d_id` FOREIGN KEY (`dep_id`) REFERENCES `depmt` (`id`))

(2)步骤二:创建级联更新和级联删除:

--补充知识:建表后的删除外键和创建外键操作
删除:alter table 表名 drop foreign key 外键名;
创建:alter table 表名 add constraint  外键名 foreign key (本表列名) reference 主表名(主表列名);
级联更新和级联删除:	
			1. 级联更新:ON UPDATE CASCADE 
			2. 级联删除:ON DELETE CASCADE
--对StInfo创建级联更新和级联删除
alter table StInfo drop foreign key s_d_id;--删除外键
alter table StInfo add constraint s_d_id foreign key (dep_id) references depmt(id) on update cascade on delete cascade;--创建外键、更新删除级联
--级联创建完成了!验证一下
--将depmt表id=1修改为3,观察StInfo表中dep_id=1的是否变为3
update depmt set id=3 where id=1;
select* from depmt;
select* from StInfo;

在这里插入图片描述
在这里插入图片描述

--在depmt中删除id=3的行,发现StInfo中dep_id=3的也被删除了
delete from depmt where id =3;
select* from depmt;
select* from StInfo;

在这里插入图片描述
在这里插入图片描述
**总结:(1)添加外键后,消除了部分数据冗余,只需要将depmt系表中的计算机系修改为计算机部,只改动了一个数据即可.
(2)添加级联后,提升了工作效率,只需要修改或删除depmt中的id,StInfo中所有dep_id=id的都发生改变。
**



这篇关于数据库外键和级联操作的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!


扫一扫关注最新编程教程