mysql外键的使用foreign key

说明

外键约束在innodb存储引擎上支撑。如果是之前的MyISAM则不支持。
该文章中使用的mysql版本为5.7.20，至于更高版本是否存在细微差异本文不做具体分析。
文中有讲述不对的请随时留言指正。

外键约束的作用

通俗来讲，外键约束就是保证了数据的完整性。
数据来讲，绑定了外键约束的列的值来源于主表。
外键约束是基于多表的，或者说创建/添加外键约束前至少要有两张表，创建/添加了外键约束后，这两张就会以父子关系存在，或者称之为主/存关系、主/子关系。

语法

foreign key(字段列) references 主表(主表列) [on delete/update cascade/set null/restrict]

foreign key 外键约束
references 关键字（引用）
cascade 同步主表动作，主表删除，子表同步删除，主表更新，子表也跟着更新。
set null 从表对应的数据设为null值。
restrict 拒绝主表更新/删除，用了该参数，主表无法删除，更新。

要领

一般来说，哪张表添加了外键约束foreign key，哪张表就是子表，而关键字references后面跟的就是主表。或者可以理解为外键约束是在子表上创建/添加的。
创建/添加外键约束，外键约束的名字是系统生成的，需要注意，而且我们再创建活添加的时候不需要给外键约束取名。
前面说到，绑定了外键约束的列的值来源于主表。那么，在创建表、插入数据的时候就需要考虑到一些情况，具体如下：
1、没有任何表的情况下：要先创建主表，再创建子表。（没有主表，在子表上创建外键约束就会没法指定主表字段，因为此时主表还没有创建）。

-- 先创建主表
create table bm01(
bm_id int PRIMARY key auto_increment,
bm_name varchar(20) not null);

-- 再创建子表
-- 最后一行的第一个bm_id是yg01表的字段，第二个bm_id是主表bm01表中的字段。
-- 最后一行意思为: yg01表的bm_id字段的值来源于bm01表中bm_id字段值
create table yg01(
yg_id int primary KEY auto_increment,
yg_name varchar(20) not null,
bm_id int,
FOREIGN key(bm_id) references bm01(bm_id);

2、有空表，有外键约束的情况下插入数据：要先给主表插入数据，在给子表插入数据。并且，子表中的关联字段的值必须在主表中存在。

-- 插入数据。
-- 注意：要先给主表写数据，然后再给子表写数据。如果直接给子表写数据，是会失败的，原因在于外键约束，其字段bm_id的值来源于主表bm_id的值，此时主表都没有数据，所以会报错。
-- 其次：子表中外键字段的值必须是在主表中存在的，如，子表插入数据bm_id值为100，而主表bm_id的值没有100也会插入不进去。
insert into bm01 values(10,'财务部'),(20,'人力资源部'),(30,'研发部'),(40,'网络安全部'),(50,'销售部');
insert into yg01 values(1,'张一',10),(2,'张二',20),(3,'张三',30),(4,'张四',40),(5,'张五',40),(6,'张六',50);

3、有表，有数据但没有外键的情况下添加外键：要注意，要选定作为外键列的数据和对应主表列的数据相匹配。也就是说，外键字段的值，主表必须存在。

alter table yg01 add foreign key(bm_id) references bm01(bm_id);

如果是如下情况，就会添加外键失败，因为子表bm_id字段值60在主表中不存在（外键字段为bm_id）
主表bm01

bm_id	name
10	财务部
20	人力资源部
30	研发部
40	网络安全部
50	销售部

子表yg01

yg_id	yg_name	bm_id
1	张一	10
2	张二	20
3	张三	30
4	张四	60

场景用法

基础用法

1、创建外键

create table yg01(
yg_id int primary KEY auto_increment,
yg_name varchar(20) not null,
bm_id int,
FOREIGN key(bm_id) references bm01(bm_id));

2、添加外键

-- 第一个bm_id是绑定外键的字段，第二个bm_id是主表的列，bm01是主表
alter table yg01 add foreign key(bm_id) references bm01(bm_id);

3、查询外键

-- sxl_01表示库名，yg01是表名
select * from information_schema.KEY_COLUMN_USAGE where table_schema = 'sxl_01' and table_name = 'yg01'

4、删除外键

-- yg01_ibfk_1是外键约束名字，可通过上面的3查询外键获取。
alter table yg01 drop foreign key yg01_ibfk_1;

高级用法

-- 添加外键约束，主表删除，子表也会跟着删除。
alter table yg01 add foreign key(bm_id) references bm01(bm_id) on delete cascade;

-- 添加外键约束，主表删除，子表匹配的项变为空。
alter table yg01 add foreign key(bm_id) references bm01(bm_id) on delete set null;

-- 添加外键约束，拒绝主表任何操作，restrict表示拒绝主表更新和删除。
alter table yg01 add foreign key(bm_id) references bm01(bm_id) on delete restrict on update restrict;

-- 级联，添加外键约束，主表删除，子表设为null，主表更新，子表同步更新。
alter table yg01 add foreign key(bm_id) references bm01(bm_id) on delete set null on update cascade;

-- 添加外键约束，主表更新，子表也会跟着更新。
alter table yg01 add foreign key(bm_id) references bm01(bm_id) on update cascade;

-- 级联，添加外键约束，主表更新，子表也会跟着更新，主表删除，子表跟着删除
alter table yg01 add foreign key(bm_id) references bm01(bm_id) on update cascade on delete cascade;

-- 级联，添加外键约束，拒绝主表删除操作，允许主表更新，子表同步更新。
alter table yg01 add foreign key(bm_id) references bm01(bm_id) on delete restrict on update cascade;