MySQL外键约束的实例讲解

2023-07-12 10:05:04 81

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

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

mysql:yeyztest::>>createtablefk_test_1(
->idintnotnullprimarykeyauto_increment,
->namevarchar()default'');
QueryOK,rowsaffected(0.10sec)

mysql:yeyztest::>>createtablefk_test_2(
->idintnotnullprimarykeyauto_increment,
->uidint,
->foreignkeyfk_uid(uid)referencesfk_test_1(id));
QueryOK,rowsaffected(0.06sec)

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

mysql:yeyztest::>>insertintofk_test_1values(,'aaa'),(,'bbb');
QueryOK,rowsaffected(0.00sec)
Records:Duplicates:Warnings:

mysql:yeyztest::>>select*fromfk_test_1;
+----+------+
|id|name|
+----+------+
||aaa|
||bbb|
+----+------+
rowsinset(0.00sec)

mysql:yeyztest::>>insertintofk_test_2values(,),(,);
QueryOK,rowsaffected(0.00sec)
Records:Duplicates:Warnings:

mysql:yeyztest::>>insertintofk_test_2values(,);
QueryOK,rowaffected(0.00sec)


mysql:yeyztest::>>insertintofk_test_2values(,);
ERROR():Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`yeyztest`.`fk_test_2`,CONSTRAINT`fk_test_2_ibfk_1`FOREIGNKEY(`uid`)REFERENCES`fk_test_1`(`id`))

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

再来看看删除的情况，

mysql:yeyztest::>>select*fromfk_test_2;
+----+------+
|id|uid|
+----+------+
|||
|||
|||
+----+------+
rowsinset(0.00sec)

mysql:yeyztest::>>deletefromfk_test_2whereid=;
QueryOK,rowaffected(0.00sec)

mysql:yeyztest::>>select*fromfk_test_1;
+----+------+
|id|name|
+----+------+
||aaa|
||bbb|
+----+------+
rowsinset(0.00sec)

mysql:yeyztest::>>deletefromfk_test_1whereid=;
ERROR():Cannotdeleteorupdateaparentrow:aforeignkeyconstraintfails(`yeyztest`.`fk_test_2`,CONSTRAINT`fk_test_2_ibfk_1`FOREIGNKEY(`uid`)REFERENCES`fk_test_1`(`id`))

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

既然delete不成功，试试update，

mysql:yeyztest::>>updatefk_test_1setid=whereid=;
ERROR():Cannotdeleteorupdateaparentrow:
aforeignkeyconstraintfails(`yeyztest`.`fk_test_2`,
CONSTRAINT`fk_test_2_ibfk_1`FOREIGNKEY(`uid`)REFERENCES`fk_test_1`(`id`))

mysql:yeyztest::>>updatefk_test_1setname='ccc'whereid=;
QueryOK,rowaffected(0.00sec)
Rowsmatched:Changed:Warnings:

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

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

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

delete父表的情况：

cascade，setnull，noaction，restrict

update父表的情况：

cascade，setnull，noaction，restrict

其中

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

设置关联的语法如下：

altertable表名addconstraintFK_IDforeignkey(外键字段名)references外表表名(主键字段名)
[ondelete{cascade|setnull|noaction|restrict}]
[onupdate{cascade|setnull|noaction|restrict}]

现在我们测试一下这其他三种情况，首先看cascade的情况：

mysql:yeyztest::>>select*fromfk_test_1;
+----+------+
|id|name|
+----+------+
||ccc|
||bbb|
+----+------+
rowsinset(0.00sec)

mysql:yeyztest::>>select*fromfk_test_2;
+----+------+
|id|uid|
+----+------+
|||
|||
+----+------+
rowsinset(0.00sec)

mysql:yeyztest::>>showcreatetablefk_test_2\G
***************************1.row***************************
Table:fk_test_2
CreateTable:CREATETABLE`fk_test_2`(
`id`int()NOTNULLAUTO_INCREMENT,
`uid`int()DEFAULTNULL,
PRIMARYKEY(`id`),
KEY`fk_uid`(`uid`),
CONSTRAINT`fk_test_2_ibfk_1`FOREIGNKEY(`uid`)REFERENCES`fk_test_1`(`id`)
)ENGINE=InnoDBAUTO_INCREMENT=DEFAULTCHARSET=utf8
rowinset(0.00sec)

mysql:yeyztest::>>altertablefk_test_2dropforeignkeyfk_test_2_ibfk_1;
QueryOK,rowsaffected(0.02sec)
Records:Duplicates:Warnings:

mysql:yeyztest::>>altertablefk_test_2addconstraintfk_uidforeignkey(uid)referencesfk_test_1(id)ondeletecascade;
QueryOK,rowsaffected(0.03sec)
Records:Duplicates:Warnings:

#######################################
####此处删除父表id=的记录，查看子表的结果###
#######################################
mysql:yeyztest::>>deletefromfk_test_1whereid=;
QueryOK,rowaffected(0.00sec)

mysql:yeyztest::>>select*fromfk_test_1;
+----+------+
|id|name|
+----+------+
||ccc|
+----+------+
rowinset(0.00sec)

mysql:yeyztest::>>select*fromfk_test_2;
+----+------+
|id|uid|
+----+------+
|||
+----+------+
rowinset(0.00sec)

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

在看一眼setnull的情况：

mysql:yeyztest::>>altertablefk_test_2dropforeignkeyfk_uid;
QueryOK,rowaffected(0.02sec)
Records:Duplicates:Warnings:

mysql:yeyztest::>>altertablefk_test_2addCONSTRAINT`fk_uid`FOREIGNKEY(`uid`)REFERENCES`fk_test_1`(`id`)ONDELETEsetnull;
QueryOK,rowaffected(0.03sec)
Records:Duplicates:Warnings:

mysql:yeyztest::>>deletefromfk_test_1whereid=;
QueryOK,rowaffected(0.00sec)

mysql:yeyztest::>>select*fromfk_test_1;
Emptyset(0.00sec)

mysql:yeyztest::>>select*fromfk_test_2;
+----+------+
|id|uid|
+----+------+
||NULL|
+----+------+
rowinset(0.00sec)

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

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

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

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

mysql:yeyztest::>>insertintofk_test_1values(,);
QueryOK,rowaffected(0.00sec)

mysql:yeyztest::>>select*fromfk_test_2;
+----+------+
|id|uid|
+----+------+
||NULL|
+----+------+
rowinset(0.00sec)

mysql:yeyztest::>>insertintofk_test_2values(,NULL);
QueryOK,rowaffected(0.00sec)

mysql:yeyztest::>>insertintofk_test_2values(,NULL);
QueryOK,rowaffected(0.00sec)

mysql:yeyztest::>>select*fromfk_test_2;
+----+------+
|id|uid|
+----+------+
||NULL|
||NULL|
||NULL|
+----+------+
rowsinset(0.00sec)

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

MySQL外键约束的实例讲解

热门推荐

随机推荐