数据准备
-- 在employee中添加外键约束
alter table employee add foreign key(dept_id) references department(id);
为什么要级联操作
-- 更新部门表中id为2的部门为id=5
-- Cannot delete or update a parent row: a foreign key constraint fails (不能直接修改主表中主键)
update department set id=5 where id=2;
-- 要删除部门id等于1的部门, 能不能直接删除呢?
-- Cannot delete or update a parent row: a foreign key constraint fails(如果有从表引用了主表中主键,主表中这条记录不能删除)
delete from department where id=1;
什么是级联操作
修改主表中主键,或者删除主表中记录的时候,从表中外键也被同时修改、或者删除
语法
写在外键约束的后面,在创建外键约束的时候创建级联操作
级联操作 | 语法 |
---|---|
级联更新 | on update cascade |
级联删除 | on delete cascade |
应用
-- 删除外键约束
alter table employee drop foreign key employee_ibfk_1;
-- 添加外键约束,级联更新和级联删除
alter table employee add constraint fk_emp_dept foreign key (dept_id) references department(id) on update cascade on delete cascade;
-- delete from employee where id > 6;
select * from employee;
-- 把部门表中id等于1的部门改成id等于9
update department set id=9 where id=1;
-- 删除部门号是2的部门
delete from department where id=2;