怎么看mysql有没有外键约束(MySQL外键约束的实例讲解)
怎么看mysql有没有外键约束
MySQL外键约束的实例讲解
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外键约束的资料请关注开心学习网其它相关文章!
原文链接:https://cloud.tencent.com/developer/article/1533843
- mysql生成唯一订单号(MySQL高并发生成唯一订单号的方法实现)
- mysql8.0中如何导入数据(mysql8.0.20数据目录迁移的方法)
- mysql清空数据库所有表格(MySQL用truncate命令快速清空一个数据库中的所有表)
- mysql8.0.25.0安装配置(MySQL8.0.23免安装版配置详细教程)
- mysqlupdate怎么设置(MySQL update 语句的正确用法)
- 搭建php和mysql的运行环境(Windows环境开发PHP完整配置教程Apache+Mysql+PHP)
- mysql 用户权限配置(详解MySQL 用户权限管理)
- mysql索引的弊端(MySQL 全文索引的原理与缺陷)
- 怎么把csv文件导入mysql(mysql导入csv的4种报错的解决方法)
- 宝塔mysql怎么设置优化(宝塔面板mysql内存占用高如何优化)
- mysql数据库触发器(MySQL中使用游标触发器的方法)
- 怎么查看mysql运行日志(通过Query Profiler查看MySQL语句运行时间的操作方法)
- mysql触发器语法规则(MySQL 触发器的使用及需要注意的地方)
- mysql将字符串转换为日期的函数(Mysql中时间戳转为Date的方法示例)
- mysql8.0.18.0安装详细教程(mysql 8.0.22 下载安装配置方法图文教程)
- mysql的索引及其介绍总结(浅析MysQL B-Tree 索引)
- 这里输入关键词(怎么输入关键词搜索)
- 34岁的舒畅,就这样走到了末路,不知会不会后悔15年前的草率决定(就这样走到了末路)
- 不走心的古装造型 舒畅 毁容式 出演,萧蔷雷出新高度(不走心的古装造型)
- 嘉南传 第22集(嘉南传第22集)
- 哪版孙悟空最萌 黄渤躺萌了(哪版孙悟空最萌)
- 融入小人物的喜怒哀乐,黄渤饰演的角色为什么让人观看时欲罢不能(融入小人物的喜怒哀乐)
热门推荐
- ubuntu下vscode的使用教程(Vscode远程连接Ubuntu出错问题的解决方法)
- win7安装安卓模拟器(腾讯云服务器怎么安装安卓模拟器 可以安装安卓模拟器吗)
- vuex怎么使用数据(vuex的辅助函数该如何使用)
- python socket 设置通信协议(详解python中TCP协议中的粘包问题)
- python定时任务详解(使用Python做定时任务及时了解互联网动态)
- dedecms自定义字段(详解怎么样让DEDECMS的list标签支持weight排序的方法)
- 如何查看python beautifulsoup(Python爬虫beautifulsoup4常用的解析方法总结)
- 企业会怎样筛选简历
- C#中SortedList的用法
- sql server重建索引
排行榜
- 1
- 2
- 3
- 4
- 5
- 6
- 7
- 8
- 9