个人中心
gpt4 book ai didi

MySQL外键约束的实例讲解

转载 作者：qq735679552 更新时间：2022-09-27 22:32:09 26 4
gpt4 key购买 nike

CFSDN坚持开源创造价值，我们致力于搭建一个资源共享平台，让每一个IT人在这里找到属于你的精彩世界.

这篇CFSDN的博客文章MySQL外键约束的实例讲解由作者收集整理，如果你对这篇文章有兴趣，记得点赞哟.

 MySQL的外键约束是用来在两个表之间建立链接的，其中一个表发生变化，另外一个表也发生变化。从这个特点来看，它主要是为了保证表数据的一致性和完整性的。 对于两个通过外键关联的表，相关联字段中主键所在的表是主表，也称之为父表，外键所在的表是从表，也称之为子表，定义外键的时候需要遵守几个规则:

1、父表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则父表与子表是同一个表，这样的表称为自参照表，这种结构称为自参照。 2、必须为父表定义主键。 3、主键不能包含空值，但允许在外键中出现空值。也就是说，只要外键的每个非空值出现在指定的主键中，这个外键的内容就是正确的。 4、外键中列的数目必须和父表的主键中列的数目相同。 5、外键中列的数据类型必须和父表主键中对应列的数据类型相同。说这么多比较笼统，还是看看例子吧.

?
1
2
3
4
5
6
7
8
9
10
mysql:yeyztest ::>> create table fk_test_1(
  -> id int not null primary key auto_increment,
  -> name varchar () default '' );
Query OK, rows affected (0.10 sec)
 
mysql:yeyztest ::>> create table fk_test_2(
  -> id int not null primary key auto_increment,
  -> uid int ,
  -> foreign key fk_uid(uid) references fk_test_1(id));
Query OK, rows affected (0.06 sec)

  这里我们创建两个表，一个是fk_test_1，一个是fk_test_2,其中fk_test_2的uid列上设置外键，关联fk_test_1的表的id列，这里很明显，fk_test_1是父表，而fk_test_2是子表，接下来我们进行数据插入实验.

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
mysql:yeyztest ::>> insert into fk_test_1 values (, 'aaa' ),(, 'bbb' );
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings:
 
mysql:yeyztest ::>> select * from fk_test_1;
+ ----+------+
| id | name |
+ ----+------+
| | aaa |
| | bbb |
+ ----+------+
  rows in set (0.00 sec)
 
mysql:yeyztest ::>> insert into fk_test_2 values (,),(,);
Query OK, rows affected (0.00 sec)
Records: Duplicates: Warnings:
 
mysql:yeyztest ::>> insert into fk_test_2 values (,);
Query OK, row affected (0.00 sec)
 
 
mysql:yeyztest ::>> insert into fk_test_2 values (,); 
ERROR (): Cannot add or update a child row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))

    先在主表上插入两条数据，分别是id=1和id=2的数据，然后再子表插入数据，子表插入uid=1和uid=2的数据都能成功，而要插入uid=3的数据时提示失败，也就是说，默认情况下，子表进行插入时，插入的外键关联字段值必须是父表被关联的列包含的值。注意这里的默认情况，后续会进行说明.

   再来看看删除的情况， 。

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
mysql:yeyztest ::>> select * from fk_test_2 ;
+ ----+------+
| id | uid |
+ ----+------+
| |  |
| |  |
| |  |
+ ----+------+
  rows in set (0.00 sec)
 
mysql:yeyztest ::>> delete from fk_test_2 where id=;
Query OK, row affected (0.00 sec)
 
mysql:yeyztest ::>> select * from fk_test_1 ; 
+ ----+------+
| id | name |
+ ----+------+
| | aaa |
| | bbb |
+ ----+------+
  rows in set (0.00 sec)
 
mysql:yeyztest ::>> delete from fk_test_1 where id=;
ERROR (): Cannot delete or update a parent row: a foreign key constraint fails (`yeyztest`.`fk_test_2`, CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))

   可以看到，在子表fk_test_2上进行删除，没有出现任何问题，而在父表fk_test_1上删除时，显示无法删除id=1的值，原因是有一个外键约束存在，也就是说，默认情况下，在父表进行删除时，无法直接删除子表中已经存在依赖关联的列值。注意这里的默认情况，下面将会说明.

    既然delete不成功，试试update， 。

?
1
2
3
4
5
6
7
8
mysql:yeyztest ::>> update fk_test_1 set id= where id=;  
ERROR (): Cannot delete or update a parent row:
a foreign key constraint fails (`yeyztest`.`fk_test_2`,
CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`))
 
mysql:yeyztest ::>> update fk_test_1 set name = 'ccc' where id=;
Query OK, row affected (0.00 sec)
Rows matched: Changed: Warnings:

     可以看到，update父表的主键列还是不能成功执行，但是update其他的列，可以成功执行.

    到这里，我们已经知道，外键的存在是为了保证数据的完整和统一性，但是也带来了一点问题，那就是父表中凡是被子表依赖的列，都没办法删除了，这不是我们想要的，有一些数据确实会过期，我们有删除的需求，那么这个时候应该怎么办?

    在上面的测试中，我们反复提到一个词，就是默认情况，我们没有设置外键的删除和更新规则，这里mysql帮我们使用了最严格的的规则，那就是restrict，其实还有其他一些规则，这里全部列出来:

  • delete父表的情况：

cascade，set null，no action，restrict 。

  • update父表的情况：

cascade，set null，no action，restrict 。

   其中 。

  • restrict是默认操作，它表示拒绝父表删除或者修改外键已经被子表所依赖的列，这是最安全的设置；
  • cascade表示在父表发生删除的时候直接删除子表的记录，这是最危险的设置；
  • set null表示父表删除的时候，对子表进行null值处理；
  • no action表示父表删除的时候，子表不进行任何改动。

   设置关联的语法如下:

?
1
2
3
alter table 表名 add constraint FK_ID foreign key (外键字段名) references 外表表名 (主键字段名)
[ on delete { cascade | set null | no action | restrict }]
[ on update { cascade | set null | no action | restrict }]

   现在我们测试一下这其他三种情况，首先看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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
mysql:yeyztest ::>> select * from fk_test_1;
+ ----+------+
| id | name |
+ ----+------+
| | ccc |
| | bbb |
+ ----+------+
  rows in set (0.00 sec)
 
mysql:yeyztest ::>> select * from fk_test_2;
+ ----+------+
| id | uid |
+ ----+------+
| |  |
| |  |
+ ----+------+
  rows in set (0.00 sec)
 
mysql:yeyztest ::>>show create table fk_test_2\G
*************************** 1. row ***************************
   Table : fk_test_2
Create Table : CREATE TABLE `fk_test_2` (
  `id` int () NOT NULL AUTO_INCREMENT,
  `uid` int () DEFAULT NULL ,
  PRIMARY KEY (`id`),
  KEY `fk_uid` (`uid`),
  CONSTRAINT `fk_test_2_ibfk_1` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT= DEFAULT CHARSET=utf8
  row in set (0.00 sec)
 
mysql:yeyztest ::>> alter table fk_test_2 drop foreign key fk_test_2_ibfk_1;
Query OK, rows affected (0.02 sec)
Records: Duplicates: Warnings:
 
mysql:yeyztest ::>> alter table fk_test_2 add constraint fk_uid foreign key (uid) references fk_test_1(id) on delete cascade ;
Query OK, rows affected (0.03 sec)
Records: Duplicates: Warnings:
 
#######################################
####此处删除父表id=的记录，查看子表的结果###
#######################################
mysql:yeyztest ::>> delete from fk_test_1 where id=;
Query OK, row affected (0.00 sec)
 
mysql:yeyztest ::>> select * from fk_test_1 ;
+ ----+------+
| id | name |
+ ----+------+
| | ccc |
+ ----+------+
  row in set (0.00 sec)
 
mysql:yeyztest ::>> select * from fk_test_2 ;
+ ----+------+
| id | uid |
+ ----+------+
| |  |
+ ----+------+
  row in set (0.00 sec)

   可以看到，一开始，父表的值包含id=1和id=2的值，子表的值包含uid=2和uid=1的值，当我们删除父表的id=2的值之后，子表中uid=2的值也直接被删除了。这就是cascade的作用，也就是级联删除.

在看一眼set null的情况:

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
mysql:yeyztest ::>> alter table fk_test_2 drop foreign key fk_uid;  
Query OK, row affected (0.02 sec)
Records: Duplicates: Warnings:
 
mysql:yeyztest ::>> alter table fk_test_2 add CONSTRAINT `fk_uid` FOREIGN KEY (`uid`) REFERENCES `fk_test_1` (`id`) ON DELETE set null ;
Query OK, row affected (0.03 sec)
Records: Duplicates: Warnings:
 
mysql:yeyztest ::>> delete from fk_test_1 where id=;
Query OK, row affected (0.00 sec)
 
mysql:yeyztest ::>> select * from fk_test_1 ;
Empty set (0.00 sec)
 
mysql:yeyztest ::>> select * from fk_test_2 ;
+ ----+------+
| id | uid |
+ ----+------+
| | NULL |
+ ----+------+
  row in set (0.00 sec)

   可以看到，设置了set null之后，当父表删除id=1的值时，子表的uid的值变成了null，而没有删除记录.

no action的情况也是类似，只不过是子表的记录没有发生任何改动.

    以上是父表进行delete的操作，当父表进行update的时候，子表可以选择的情况也有以上四种，和delete基本保持一致，这里不再赘述。有兴趣可以自己测试一发.

   最后，说明一点，子表的外键列可以为空值.

?
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
mysql:yeyztest ::>> insert into fk_test_1 values (,);
Query OK, row affected (0.00 sec)
 
mysql:yeyztest ::>> select * from fk_test_2 ;  
+ ----+------+
| id | uid |
+ ----+------+
| | NULL |
+ ----+------+
  row in set (0.00 sec)
 
mysql:yeyztest ::>> insert into fk_test_2 values (, NULL );
Query OK, row affected (0.00 sec)
 
mysql:yeyztest ::>> insert into fk_test_2 values (, NULL );
Query OK, row affected (0.00 sec)
 
mysql:yeyztest ::>> select * from fk_test_2;
+ ----+------+
| id | uid |
+ ----+------+
| | NULL |
| | NULL |
| | NULL |
+ ----+------+
  rows in set (0.00 sec)

以上就是MySQL外键约束的实例讲解的详细内容，更多关于MySQL外键约束的资料请关注我其它相关文章！ 。

原文链接：https://cloud.tencent.com/developer/article/1533843 。

最后此篇关于MySQL外键约束的实例讲解的文章就讲到这里了,如果你想了解更多关于MySQL外键约束的实例讲解的内容请搜索CFSDN的文章或继续浏览相关文章，希望大家以后支持我的博客！ 。

26 4 0
文章推荐： mysql触发器原理与用法实例分析
文章推荐： mysql索引原理与用法实例分析
文章推荐： mysql视图原理与用法实例详解
文章推荐： Win10安装MySQL8压缩包版的教程
qq735679552
个人简介

我是一名优秀的程序员,十分优秀！

滴滴打车优惠券免费领取
滴滴打车优惠券
全站热门文章
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com