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

2. 删除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

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

2. 删除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);

2. 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;

3. 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),-- 当主表中的数据更新时，从表数据级联更新，当主表数据删除时，从表数据设置为Nullconstraint 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”

---