龙岗住房建设局网站,企业建筑网站,公司法人变更流程,平台推广广告宣传词一、什么是外键约束#xff1f; 外键约束#xff08;FOREIGN KEY#xff0c;缩写FK#xff09;是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来#xff0c;特别是针对修改或者删除的级联操作时#xff0c;会保证数据的完整性。 外键是指表…一、什么是外键约束 外键约束FOREIGN KEY缩写FK是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来特别是针对修改或者删除的级联操作时会保证数据的完整性。 外键是指表中某个字段的值依赖于另一张表中某个字段的值而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表设置外键约束的表称为子表或者从表。 二、外键约束示例 如果想要表示学生和班级的关系首先要有学生表和班级表两张表然后学生表中有个字段为stu_class该字段表示学生所在的班级而该字段的取值范围由班级表中的主键cla_no字段该字段表示班级编号的取值决定。那么班级表为主表学生表为从表且stu_class字段是学生表的外键。通过stu_class字段就建立了学生表和班级表的关系。 如果这样设计学生表有以下两个缺点
缺点一数据重复缺点二修改班级数据时需要更改多条记录 可以这样来设计学生表 以上
班级表被称为父表班级编号是它的主键
学生表被称为子表班级名称是它的外键。 三、外键约束的SQL展示
1、子表依赖父表因此先创建父表
create table t_class(cno int(4) primary key auto_increment,cname varchar(10) not null,room char(4)
); 2、为表class添加数据
insert into t_class values (null,Python一班,r803);
insert into t_class values (null,Python二班,r416);
insert into t_class values (null,Java一班,r103); 3、创建子表学生表t_student
创建外键时列名可以不一样但是列类型及其长度最好与主键保持一致。
create table t_student(sno int(6) primary key auto_increment,sname varchar(5) not null,classno int(4)
);4、添加学生信息
insert into t_student values (null,张三,1);
insert into t_student values (null,李四,1);
insert into t_student values (null,王五,1); 5、将主表和从表关联起来
需要添加外键约束外键约束只有表级约束没有列级约束。
为子表t_student添加外键约束指定约束名为fk_stu_classno,将t_student的外键classno和t_class的主键cno关联起来
alter table t_student add constraint fk_stu_classno foreign key (classno) references t_class (cno);
6、测试是否关联成功
目前学生表如下 班级表如下 测试一将t_class表中班级1删除
预期结果应该是无法删除的因为和班级表关联的学生表中有同学在班级1中删除班级2、3应该是可以的因为学生没有2班和3班的
delete from t_class cno1;
执行返回1451错误 1451 - Cannot delete or update a parent row: a foreign key constraint fails (database_me.t_student, CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno)) 因为受到了外键约束的影响。 测试二删除班级2 成功删除了。 测试三在学生表中添加一条数据这位同学是3班的然后尝试是否可以删除班级3
insert into t_student values (null,老六,3); 删除班级3
delete from t_class where cno3;
此时会返回1451错误主键已经被外键约束了。 测试四为设有外键的子表添加一个班级为4的同学
insert into t_student values (null,小七,4);
返回1452错误 1452 - Cannot add or update a child row: a foreign key constraint fails (database_me.t_student, CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno)) 因为主表中没有班级4。 四、删除主/从表
需要先删除从表后删除主表否则无法删除。
先删除主表会返回如下错误 3730 - Cannot drop table t_class referenced by a foreign key constraint fk_stu_classno on table t_student. 五、外键策略
因为部分操作致使班级表和学生表数据混乱现在重新创建这两张表来进行下面的演示。 需求希望删除班级2
但是直接删除是删不了因为有外键约束我们可以考虑加入外键策略。
1、策略1 no action 不允许操作 可以先将班级为2的同学的班级编号改为null
update t_student set classnonull where classno2;再删除
delete from t_class where cno2; 2、策略2cascade级联操作操作主表的时候影响从表的外键信息。
没有添加级联操作之前尝试更新班级号
update t_class set cno5 where cno3;
返回1451错误 1451 - Cannot delete or update a parent row: a foreign key constraint fails (database_me.t_student, CONSTRAINT fk_stu_classno FOREIGN KEY (classno) REFERENCES t_class (cno)) 使用cascade级联操作需要先删除之前的外键约束
alter table t_student drop foreign key fk_stu_classno;
再重新添加外键约束
alter table t_studentadd constraint fk_stu_classnoforeign key (classno) references t_class (cno)on update cascade on delete cascade
;
on update cascade on delete cascade表示在进行更新和删除时都会有级联操作。
再尝试更新班级号
update t_class set cno5 where cno3; 试试删除操作删除班级编号为5的班级删除之前可以看见学生表中有5班的同学 班级表中删除5班
delete from t_class where cno5;
查看班级表 再看学生表 由此可见级联操作主表变动时从表数据也会随之改变。级联操作要慎用它对生产库影响较大。 3、策略3set 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;
尝试更新班级表班级编号
update t_class set cno8 where cno1;
查看班级表 查看学生表 策略2的级联操作和策略3的置空操作可以混合使用如为更新操作添加的是级联操作为删除操作添加的是置空操作
alter table t_student add constraintfk_stu_classno foreign key (classno) references t_class (cno)update cascade on delete set null;
两者的应用场合有所不同例如
删除朋友圈时下面的评论也会一起删除那么朋友圈的删除操作就可以使用级联操作
解散班级时班级的同学依然存在可以将同学的班级信息置为空因为后续会为同学分配新的班级那么解散班级操作就可以添加置空操作。
