Mysql外键约束的几种建立方式与区别

*Mysql外键约束的几种建立方式与区别*

一.理论解释
外键约束的概念大概可以理解为对一张表的数据进行操作修改时受到另一张表相关联数据的影响。
① 建立外键的语法大致如下：Foreign key (【外键名】) references 【父表名】(【父表主键名】) on delete 【约束方式】 on update 【约束方式】
② 约束方式
CASCADE：表示父表在进行更新和删除时，更新和删除子表相对应的记录
RESTRICT和NO ACTION：限制在子表有关联记录的情况下，父表不能单独进行删除和更新操作
SET NULL：表示父表进行更新和删除的时候，子表的对应字段被设为NULL
二. 实战测试
1. 以CASCADE（级联）约束方式
① 创建势力表（父表）country

create table country（
id int not null,
name varchar(30),
primary key(id)
）;

② 插入记录

insert into country values(1,'西欧')；
insert into country values(2,'玛雅')；
insert into country values(3,'西西里');

③ 创建兵种表（子表）并建立约束关系

create table solider(
id int not null,
name varchar(30),
country_id int,
primary key(id),
foreign key(country_id) references country(id) on delete cascade on update cascade,
);

④ 参照完整性测试

insert into solider values(1,'西欧见习步兵',1);
#插入成功
insert into solider values(2,'玛雅短矛兵'，2);
#插入成功
insert into solider values(3,'西西里诺曼骑士',3)
#插入成功
insert into solider values(4，'法兰西剑士',4);
#插入失败，因为country表中不存在id为4的势力

⑤ 约束方式测试

insert into solider values(4，'玛雅猛虎勇士'，2);
#成功插入
delete from country where id=2；
#会导致solider表中id为2和4的记录同时被删除，因为父表中都不存在这个势力了，那么相对应的兵种自然也就消失了
update country set id=8 where id=1;
#导致solider表中country_id为1的所有记录同时也会被修改为8

2.以SET NULL约束方式
③ 创建兵种表（子表）并建立约束关系

create table solider(
id int not null,
name varchar(30),
country_id int,
primary key(id),
foreign key(country_id) references country(id) on delete set null on update set null,
);

④ 参照完整性测试

insert into solider values(1,'西欧见习步兵',1);
#插入成功
insert into solider values(2,'玛雅短矛兵'，2);
#插入成功
insert into solider values(3,'西西里诺曼骑士',3)
#插入成功
insert into solider values(4，'法兰西剑士',4);
#插入失败，因为country表中不存在id为4的势力

⑤ 约束方式测试

insert into solider values(4，'西西里弓箭手'，3);
#成功插入
delete from country where id=3；
#会导致solider表中id为3和4的记录被设为NULL
update country set id=8 where id=1;
#导致solider表中country_id为1的所有记录被设为NULL

3.以NO ACTION 或 RESTRICT（禁止）方式

③ 创建兵种表（子表）并建立约束关系

create table solider(
id int not null,
name varchar(30),
country_id int,
primary key(id),
foreign key(country_id) references country(id) on delete RESTRICT on update RESTRICT,
);

④ 参照完整性测试

insert into solider values(1,'西欧见习步兵',1);
#插入成功
insert into solider values(2,'玛雅短矛兵'，2);
#插入成功
insert into solider values(3,'西西里诺曼骑士',3)
#插入成功
insert into solider values(4，'法兰西剑士',4);
#插入失败，因为country表中不存在id为4的势力

⑤ 约束方式测试

insert into solider values(4，'西欧骑士'，1);
#成功插入
delete from country where id=1；
#发生错误，子表中有关联记录，因此父表中不可删除相对应记录，即兵种表还有属于西欧的兵种，因此不可单独删除父表中的西欧势力
update country set id=8 where id=1;
#错误，子表中有相关记录，因此父表中无法修改

三. 注意事项

① 两张表的引擎都必须是innodb，只有该引擎才支持外键
② 在mysql中，no action与restrict功能相同
③ 可以手动打开或关闭外键约束：SET FOREIGN_KEY_CHECKS = 0/1;
④ 查看外键约束可以用show create table …或者show table status like …