1、班级表
2、学生表
例1:删除班级5:如果直接删除的话肯定不行,因为有外键约束
delete from t_class where cno=5;
1451 - Cannot delete or update a parent row: a foreign key constraint fails (mytestdb
.t_student
, CONSTRAINTfk_stu_classno
FOREIGN KEY (classno
) REFERENCESt_class
(cno
))
– 加入外键策略:
– 策略1:no action 不允许操作
– 先把班级2对应的学生表中的班级改为null
update t_student set classno=null where classno=5;delete from t_class where cno=5;
– 策略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;
删除外键之后
-- 试试更新:update t_class set cno = 5 where cno = 3;-- 试试删除:delete from t_class where cno = 5;
– 策略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;
-- 试试更新:update t_class set cno = 8 where cno = 1;
更新成功
– 2.应用场合:
– (1)朋友圈删除,点赞。留言都删除 – 级联操作
– (2)解散班级,对应的学生 置为班级为null就可以了,-- set null