详解MySQL 外键约束

时间:2022-08-27 23:48:28

官方文档:
https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

1.外键作用:

MySQL通过外键约束来保证表与表之间的数据的完整性和准确性。

2.外键的使用条件

  • 两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持)
  • 外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显示建立;
  • 外键关系的两个表的列必须是数据类型相似,也就是可以相互转换类型的列,比如int和tinyint可以,而int和char则不可以。

3.创建语法

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (col_name, ...)
    REFERENCES tbl_name (col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

该语法可以在 CREATE TABLE 和 ALTER TABLE 时使用,如果不指定CONSTRAINT symbol,MYSQL会自动生成一个名字。
ON DELETE、ON UPDATE表示事件触发限制,可设参数:
RESTRICT(限制外表中的外键改动)
CASCADE(跟随外键改动)
SET NULL(设空值)
SET DEFAULT(设默认值)
NO ACTION(无动作,默认的)

CASCADE:表示父表在进行更新和删除时,更新和删除子表相对应的记录
RESTRICT和NO ACTION:限制在子表有关联记录的情况下,父表不能单独进行删除和更新操作
SET NULL:表示父表进行更新和删除的时候,子表的对应字段被设为NULL

4.案例演示

以CASCADE(级联)约束方式

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
1. 创建*表(父表)country
create table country (
id int not null,
name varchar(30),
primary key(id)
);
 
2. 插入记录
insert into country values(1,'西欧');
insert into country values(2,'玛雅');
insert into country values(3,'西西里');
 
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,
);
 
4. 参照完整性测试
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的*
 
5. 约束方式测试
 
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

以SET NULL约束方式

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1. 创建兵种表(子表)并建立约束关系
 
drop table if exists solider;
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,
);
 
2. 参照完整性测试
 
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的*
 
3. 约束方式测试
 
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

以NO ACTION 或 RESTRICT方式 (默认)

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
1. 创建兵种表(子表)并建立约束关系
 
drop table if exists solider;
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,
);
 
2. 参照完整性测试
 
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的*
 
3. 约束方式测试
 
insert into solider values(4,'西欧骑士',1);
#成功插入
delete from country where id=1;
#发生错误,子表中有关联记录,因此父表中不可删除相对应记录,即兵种表还有属于西欧的兵种,因此不可单独删除父表中的西欧*
update country set id=8 where id=1;
#错误,子表中有相关记录,因此父表中无法修改

以上就是详解MySQL 外键约束的详细内容,更多关于MySQL 外键约束的资料请关注服务器之家其它相关文章!

原文链接:https://cloud.tencent.com/developer/article/1500382