网站首页> 数据库> Mysql> MySQL外键约束的实例讲解

MySQL外键约束的实例讲解

时间:2020-11-12 12:03:55 阅读:10627次 来源:互联网

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外键约束的实例讲解的详细内容。

本文地址:https://www.manongw.com/article/262.html

文章来源:转载于腾讯云,转载网址为https://cloud.tencent.com/developer/article/1533843

版权申明:本文内容由互联网用户自发贡献,该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容, 请发送邮件至 ezhongheng@126.com 举报,一经查实,本站将立刻删除。

相关文章
  • 本文主要介绍了MySQL外键约束的实例讲解的方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...
    2020-11-12 12:03
  • 本文主要介绍了MySQL数据库备份过程的注意事项的方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...
    2020-11-07 17:00
  • 本文主要介绍了mysql实现从导出数据的sql文件中只导入指定的一个表的方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...
    2020-11-10 17:05
  • 本文主要介绍了MySQL查看与修改字符集的方法实例教程的方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...
    2020-10-18 17:01
  • 本文主要介绍了MySQL 基于时间点的快速恢复方案的方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...
    2020-11-12 11:50
  • 本文主要介绍了MySQL Select语句是如何执行的的方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...
    2020-11-07 17:06
  • 本文主要介绍了mysql CPU高负载问题排查的方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...
    2020-11-08 08:20
  • 本文主要介绍了mySQL之关键字的执行优先级讲解的方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...
    2020-11-10 16:14
  • 本文主要介绍了详解mysql中的严格模式和非严格模式的方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...
    2020-10-20 17:46
  • 本文主要介绍了mybatis-plus分页传入参数后sql where条件没有limit分页信息操作的方法,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有一定的参考学习价值,需要的朋友们下面随着小编来一起学习学习吧...
    2020-11-30 10:40