表的完整性约束-外键约束
- 外键约束(FOREIGN KEY，缩写FK)是用来实现数据库表的参照完整性的。外键约束可以使两张表紧密的结合起来，特别是针对修改或者删除的级联操作时，会保证数据的完整性。
- 外键是指表中某个字段的值依赖于另一张表中某个字段的值，而被依赖的字段必须具有主键约束或者唯一约束。被依赖的表我们通常称之为父表或者主表，设置外键约束的表称为子表或者从表。
代码示例
- 先创建一个部门表(父表)
-- 先创建父表：部门表：
create table department(
dno int(4) primary key auto_increment,
dname varchar(10) not null,
room char(4)
)- 创建员工表,并添加外键约束外键约束只有表级约束,没有列级约束
-- 注意：外键约束只有表级约束，没有列级约束：
-- 方法一:创建表的时候添加外键约束
create table staff(
eid int(6) primary key auto_increment,
name varchar(5) not null,
department int(4),
-- 取值参考department表中的dno字段，不要求字段名字完全重复，但是类型长度定义 尽量要求相同。
constraint fk_staff_department foreign key (department) references department (dno)
);
-- 方法二:创建完表后添加外键约束
create table staff(
eid int(6) primary key auto_increment,
name varchar(5) not null,
department int(4)
);
-- 在创建表以后添加外键约束：
alter table staff add constraint fk_staff_department foreign key (department) references department (dno)外键策略
-
对于有外键约束的表格,可以设置外键策略来达到在操作父表的时候,子表也响应的作出策略定义的修改
-
外键策略有三个
- 策略1：no action 不允许操作
-- 外键策略 no action delete from department where dno = 2; -- > 1451 - Cannot delete or update a parent row: a foreign key constraint fails (`mytestdb`.`staff`, CONSTRAINT `fk_staff_department` FOREIGN KEY (`department`) REFERENCES `department` (`dno`)) -- 默认删除的外键策略是no action 如果删除删除有员工的部门的话,就会出错,不让直接删除- 策略2：cascade 级联操作：主表中的数据被修改,那么子表关联的字段也跟着主表一起修改,比如父表中部门编号被修改,那么子表中的部门也会相应的被修改
-- 先删除之前的外键约束： alter table staff drop foreign key fk_staff_department; -- 重新添加外键约束： alter table staff add constraint fk_staff_department foreign key (department) references department (dno) on update cascade on delete cascade; -- on update cascade 与 on delete cascade; -- 表示修改,与删除都设置为级联策略 -- 那么部门表中,更改了部门编号,子表中部门编号就跟着自动修改, -- 要是删除了某个部门,那么这个部门的所有员工信息也都降被删除.- 策略3：set null 置空操作：主表数据被修改,那么子表关联的字段设置为空,比如父表中某个部门被删除,设置了set null后,子表中该部门的员工信息部门字段会被设置为null,如果不设置为set null的话,父表部门被删除,子表中该部门的员工信息也同时被删除
-- 先删除之前的外键约束： alter table staff drop foreign key fk_staff_department; -- 重新添加外键约束： alter table staff add constraint fk_staff_department foreign key (department) references department (dno) on update set null on delete set null; -- on update set null 与 on delete set null; -- 表示修改,与删除都设置为set null策略 -- 那么部门表中,更改了部门编号,子表中该部门员工的部门字段都被修改为null, -- 要是删除了某个部门,那么这个部门的所有员工部门字段都被修改为null. -
一般策略2与策略3搭配进行使用,修改的时候,级联进行修改,删除的时候set null
-- 先删除之前的外键约束： alter table staff drop foreign key fk_staff_department; -- 重新添加外键约束： alter table staff add constraint fk_staff_department foreign key (department) references department (dno) on update cascade on delete set null; -- on update cascade on delete set null; -- 表示修改使用级联策略,删除使用set null策略 -- 那那么部门表中,更改了部门编号,子表中部门编号就跟着自动修改, -- 要是删除了某个部门,那么这个部门的所有员工部门字段都被修改为null.
