数据库外键和级联操作

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的都发生改变。
**