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

(0)

相关推荐

  • 详解MySQL中的外键约束问题

    使用MySQL开发过数据库驱动的小型web应用程序的人都知道,对关系数据库的表进行创建.检索.更新和删除等操作都是些比较简单的过程.理论上,只要掌握了最常见的SQL语句的用法,并熟悉您选择使用的服务器端脚本语言,就足以应付对MySQL表所需的各种操作了,尤其是当您使用了快速MyISAM数据库引擎的时候.但是,即使在最简单的情况下,事情也要比我们想象的要复杂得多.下面我们用一个典型的例子进行说明.假设您正在运行一个博客网站,您几乎天天更新,并且该站点允许访问者评论您的帖子. MySQL外键约束条件

  • MySQL删除有外键约束的表数据方法介绍

    在MySQL中删除一张表或一条数据的时候,出现 [Err] 1451 -Cannot deleteorupdatea parent row: aforeignkeyconstraintfails (...) 这是因为MySQL中设置了foreign key关联,造成无法更新或删除数据.可以通过设置FOREIGN_KEY_CHECKS变量来避免这种情况. 禁用外键约束,我们可以使用: SETFOREIGN_KEY_CHECKS=0; 然后再删除数据 启动外键约束,我们可以使用: SETFOREIG

  • MySQL外键约束常见操作方法示例【查看、添加、修改、删除】

    本文实例讲述了MySQL外键约束常见操作方法.分享给大家供大家参考,具体如下: 1. 查看数据库表创建的sql语句 show create table vip 2. 查看外键的约束名 CREATE TABLE `vip` ( `id` int(11) NOT NULL AUTO_INCREMENT, `address` varchar(255) DEFAULT NULL, `code` varchar(255) DEFAULT NULL, `mobile` varchar(255) DEFAUL

  • MySQL删除表的时候忽略外键约束的简单实现

    删除表不是特别常用,特别是对于存在外键关联的表,删除更得小心.但是在开发过程中,发现Schema设计的有问题而且要删除现有的数据库中所有的表来重新创建也是常有的事情:另外在测试的时候,也有需要重新创建数据库的所有表.当然很多自动化工具也可以做这样的事情. 删除表的时候有时会遇到这样的错误消息: ERROR 1217 (23000): Cannot delete or update a parent row: a foreign key constraint fails 这是因为你尝试删除的表中的

  • MySQL外键约束的禁用与启用命令

    MySQL外键约束的禁用与启用: MySQL外键约束是否启用是通过一个全局变量标示的: FOREIGN_KEY_CHECKS=0; 禁用状态 FOREIGN_KEY_CHECKS=1; 启用状态 查看当前FOREIGN_KEY_CHECKS的值可用如下命令: SELECT @@FOREIGN_KEY_CHECKS; 禁用外键约束: SET FOREIGN_KEY_CHECKS=0; 启用外键约束: SET FOREIGN_KEY_CHECKS=1; 以上这篇MySQL外键约束的禁用与启用命令就是

  • 详解MySQL 外键约束

    官方文档: https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html 1.外键作用: MySQL通过外键约束来保证表与表之间的数据的完整性和准确性. 2.外键的使用条件 两个表必须是InnoDB表,MyISAM表暂时不支持外键(据说以后的版本有可能支持,但至少目前不支持) 外键列必须建立了索引,MySQL 4.1.2以后的版本在建立外键时会自动创建索引,但如果在较早的版本则需要显示建立: 外键关系的两个表的列必须

  • MySQL 关闭子表的外键约束检察方法

    准备: 定义一个教师表.一个学生表:在学生表中引用教师表ID create table teachers(teacherID int not null auto_increment primary key,teacherName varchar(8)); create table students(studentID int not null auto_increment primary key,teacherID int not null,studentName varchar(8), con

  • MySQL外键约束的实例讲解

    MySQL的外键约束是用来在两个表之间建立链接的,其中一个表发生变化,另外一个表也发生变化.从这个特点来看,它主要是为了保证表数据的一致性和完整性的. 对于两个通过外键关联的表,相关联字段中主键所在的表是主表,也称之为父表,外键所在的表是从表,也称之为子表,定义外键的时候需要遵守几个规则: 1.父表必须已经存在于数据库中,或者是当前正在创建的表.如果是后一种情况,则父表与子表是同一个表,这样的表称为自参照表,这种结构称为自参照. 2.必须为父表定义主键. 3.主键不能包含空值,但允许在外键中出现

  • MySQL外键约束(FOREIGN KEY)案例讲解

    MySQL 外键约束(FOREIGN KEY)是表的一个特殊字段,经常与主键约束一起使用.对于两个具有关联关系的表而言,相关联字段中主键所在的表就是主表(父表),外键所在的表就是从表(子表). 外键用来建立主表与从表的关联关系,为两个表的数据建立连接,约束两个表中数据的一致性和完整性.比如,一个水果摊,只有苹果.桃子.李子.西瓜等 4 种水果,那么,你来到水果摊要买水果就只能选择苹果.桃子.李子和西瓜,其它的水果都是不能购买的. 主表删除某条记录时,从表中与之对应的记录也必须有相应的改变.一个表

  • MySQL 外键约束和表关系相关总结

    目录 外键(Foreign Key) 如何确定表关系 如何建立表关系 一对多关系 - 员工表和部门表 多对多 一对一 表关系总结 外键(Foreign Key) 按照上述所说,一张表存储员工信息会极大的浪费资源,重复数据太多,这个问题就类似于将所有的代码都写在了一个py文件中,因此我们可以将一个表拆成不同的表,在这不同的表之间建立关联,而建立关联就需要使用外键foreign key.外键也属于约束条件的一种. 如何确定表关系 表与表之间的关系有三种一对多.多对多.一对一.那么如何确定表与表之间的

  • Mysql外键约束的创建与删除的使用

    目录 创建表时创建外键 给存在的表添加外键 删除外键约束 创建表时创建外键 创建两个表格, 一个名为class, create table classes( id int not null primary key, name varchar(30) ); 另一个名为student create table student( sid int not null primary key, sname varchar(30), cid int not null, constraint fk_cid fo

  • MySQL外键约束(Foreign Key)案例详解

    目录 一.MySQL外键约束作用 二.外键约束创建 (一)创建外键约束的条件 (二)在创建数据表时创建外键约束 (三)在创建数据表后添加外键约束 三.外键约束功能演示 总结 今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL外键约束详解. 一.MySQL外键约束作用 外键约束(Foreign Key)即数据库中两个数据表之间的某个列建立的一种联系.这种联系通常是以实际场景中含义完全相同的字段所造成的.MySQL通过外键约束的引入,可以使得数据表中的数据完整性更强,也更符合显示情况.下

  • MySQL外键约束详解

    目录 一.MySQL外键约束作用 二.外键约束创建 (一)创建外键约束的条件 (二)在创建数据表时创建外键约束 (三)在创建数据表后添加外键约束 三.外键约束功能演示 今天继续给大家介绍MySQL相关知识,本文主要内容是MySQL外键约束详解. 一.MySQL外键约束作用 外键约束(Foreign Key)即数据库中两个数据表之间的某个列建立的一种联系.这种联系通常是以实际场景中含义完全相同的字段所造成的.MySQL通过外键约束的引入,可以使得数据表中的数据完整性更强,也更符合显示情况.下面,我

  • PostgreSQL中enable、disable和validate外键约束的实例

    我就废话不多说了,大家还是直接看实例吧~ postgres=# create table t1(a int primary key,b text,c date); CREATE TABLE postgres=# create table t2(a int primary key,b int references t1(a),c text); CREATE TABLE postgres=# insert into t1 (a,b,c) values(1,'aa',now()); INSERT 0

随机推荐