MySQL外键约束的实例讲解

数据库 2024/11/23 佚名

3 2 1

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

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

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是子表，接下来我们进行数据插入实验。

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的数据时提示失败，也就是说，默认情况下，子表进行插入时，插入的外键关联字段值必须是父表被关联的列包含的值。注意这里的默认情况，后续会进行说明。

再来看看删除的情况，

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，

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表示父表删除的时候，子表不进行任何改动。

设置关联的语法如下：

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的情况：

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的情况：

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基本保持一致，这里不再赘述。有兴趣可以自己测试一发。

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

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外键约束的资料请关注其它相关文章！

MySQL,外键约束

标签：

MySQL,外键约束

免责声明：本站文章均来自网站采集或用户投稿，网站不提供任何软件下载或自行开发的软件！如有用户或公司发现本站内容信息存在侵权行为，请邮件告知！ 858582#qq.com

评论“MySQL外键约束的实例讲解”

MySQL外键约束的实例讲解

暂无“MySQL外键约束的实例讲解”评论...

www.imxmx.com 杰晶网络

8,675无损音乐

1,324高清电影

213破解软件

120,141站长资源

MySQL外键约束的实例讲解

MySQL,外键约束

MySQL 基于时间点的快速恢复方案

MySQL用truncate命令快速清空一个数据库中的所有表

评论“MySQL外键约束的实例讲解”

RTX 5090要首发性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

MySQL外键约束的实例讲解

MySQL,外键约束

MySQL 基于时间点的快速恢复方案

MySQL用truncate命令快速清空一个数据库中的所有表

评论“MySQL外键约束的实例讲解”

RTX 5090要首发 性能要翻倍！三星展示GDDR7显存

更新动态

友情链接

RTX 5090要首发性能要翻倍！三星展示GDDR7显存