【MySQL基础】章节15：完整性约束之外键约束

【1】什么是外键约束

外键约束（FOREIGN KEY，缩写FK）是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。

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

如下图，将原来的学生表拆分成班级表和学生表两张表，将班级信息在班级表中单独管理。班级表为主表，学生表为从表，且班级编号字段是班级表的主键，是学生表的外键。通过班级编号字段就建立了学生表和班级表的参照关系（有点像c语言里指针的感觉）。

【2】代码演示外键约束

注意：外键约束只有表级约束，没有列级约束

create table t_class(
        cno int(4) primary key auto_increment,
        cname varchar(10) not null,
        room char(4)
);
insert into t_class values (null,'java001','r803'),(null,'java002','r416'),(null,'大数据001','r103');
create table t_student(
        sno int(6) primary key auto_increment, 
        sname varchar(5) not null, 
        classno int(4),-- 取值参考t_class表中的cno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
        constraint fk_stu_classno foreign key (classno) references t_class (cno)
        -- foreign key后面的括号里填这张表的外键
        -- references（注意有s）后面的括号里填参照的表，以及参照的表里的哪个字段（这个字段必须有主键约束或者唯一约束）
);
insert into t_student values (null,'张三',1),(null,'李四',1),(null,'王五',2),(null,'朱六',2),(null,'赵七',3),(null,'吴八',3),(null,'赵信',4),(null,'亚索',4);

  
insert into t_student values(null,'小明',5);
-- 报错，由于外键约束，t_class班级里根本没有可参照的班级编号为5的班级 
delete from t_class where cno=2;
-- 报错，由于外键约束，t_student里面的班级编号字段是参照t_class的，如果删掉了t_class里班级编号为2的班级，那t_student里的班级编号要怎么办呢？所以不允许该操作
update t_class set cno=5 where cno=2;
-- 报错，这些学生明明记得自己是2班的，现在突然把2班改成5班，也是不允许的

【3】外键策略

在上面的例子中，由于加入了外键约束，不允许直接删除2班或者将2班班级编号改成别的编号，需要加入外键策略，下面用代码演示外键策略

-- 策略1：先把原2班的学生都合并到1班去，让2班没有人，再删除2班
update t_student set classno=1 where classno=2;
delete from t_class where cno=2;

-- 策略2：cascade级联操作：操作主表的同时影响从表的外键信息；
-- 先删除之前的外键约束
alter table t_student drop foreign key fk_stu_classno;
-- 重新添加包含级联操作的外键约束
alter table t_student add constraint fk_stu_classno foreign key(classno) references t_class(cno) on update cascade on delete cascade;
-- on update cascade代表在修改主表的时候进行级联操作
-- on delete cascade代表在删除主表的时候进行级联操作
-- 将2班变成5班
update t_class set cno=5 where cno=2;
--查看此时的学生信息，发现2班的学生都变成5班了
select * from t_student;
-- 删除1班
delete from t_class where cno=1;
--查看此时的学生信息，发现1班的学生都不见了（1班的学生随着1班的删除也删除了）
select * from t_student;

-- 策略3：set null 置空操作
-- 先删除之前的外键约束：
alter table t_student drop foreign key fk_stu_classno;
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno) on update set null on delete set null;
-- on update set null代表在修改主表的时候,从表的外键变成null
-- on delete set null代表在删除主表的时候,从表的外键变成null
-- 将4班编号改成8班
update t_class set cno=8 where cno=4;
--查看此时的学生信息，发现原先4班学生的班级编号现在是null
select * from t_student;
-- 删除5班
delete from t_class where cno=5;
-- 查看此时的学生信息，发现原先5班学生的班级编号现在是null，学生并没有被删
select * from t_student;

ps：
1.update和delete的策略不一定要相同，可以混合使用
2.具体使用哪种策略取决于应用场景，例如：
1）朋友圈删除，则对应的点赞，留言信息都删除 – 级联操作
2）解散班级，对应的学生置班级为null，学生不删除 – set null