MariaDB数据库的外键约束实例详解

2023-09-08 10:55:05 157

外键

外键的用途是确保数据的完整性。它通常包括以下几种：

1实体完整性，确保每个实体是唯一的（通过主键来实施）

2域完整性，确保属性值只从一套特定可选的集合里选择

3关联完整性，确保每个外键或是NULL（如果允许的话）或含有与相关主键值相配的值

1.什么是外键约束

与主键约束不同，创建外键约束不会自动创建对应的索引。但是由于以下原因，对外键手动创建索引通常是有用的：

当在查询中组合相关表中的数据时，经常在联接条件中使用外键列，方法是将一个表的外键约束中的一列或多列与另一个表中的主键列或唯一键列匹配。索引使数据库引擎可以在外键表中快速查找相关数据。但是，创建此索引并不是必需的。即使没有对两个相关表定义主键或外键约束，也可以对来自这两个表中的数据进行组合，但两个表间的外键关系说明已用其键作为条件对其进行了优化，以便组合到查询中。
对主键约束的更改可由相关表中的外键约束检查。

外键约束(foreignkey)就是表与表之间的某种约定的关系，由于这种关系的存在，我们能够让表与表之间的数据，更加的完整，关连性更强。

关于数据表的完整性和关连性，可以举个例子

有二张表，一张是用户表，一张是订单表：

1.如果我删除了用户表里的用户，那么订单表里面跟这个用户有关的数据，就成了无头数据了，不完整了。
2.如果我在订单表里面，随便插入了一条数据，这个订单在用户表里面，没有与之对应的用户。这样数据也不完整了。

如果有外键的话，就方便多了，可以不让用户删除数据，或者删除用户的话，通过外键同样删除订单表里面的数据，这样也能让数据完整。

通过外键约束，每次插入或更新数据表时，都会检查数据的完整性。

2.创建外键约束

2.1方法一：通过createtable创建外键

语法：

createtable数据表名称(
...,
[CONSTRAINT[约束名称]]FOREIGNKEY[外键字段]
REFERENCES[外键表名](外键字段，外键字段2…..)
[ONDELETECASCADE]
[ONUPDATECASCADE]
)

参数的解释：

RESTRICT:拒绝对父表的删除或更新操作。
CASCADE:从父表删除或更新且自动删除或更新子表中匹配的行。ONDELETECASCADE和ONUPDATECASCADE都可用

注意：onupdatecascade是级联更新的意思，ondeletecascade是级联删除的意思，意思就是说当你更新或删除主键表，那外键表也会跟随一起更新或删除。

精简化后的语法：

foreignkey当前表的字段references外部表名(关联的字段）type=innodb

2.1.1插入测试数据

例子：我们创建一个数据库，包含用户信息表和订单表

MariaDB[book]>createdatabasemarket;#创建market数据库
QueryOK,1rowaffected(0.00sec)

MariaDB[book]>usemarket;#使用market数据库
Databasechanged

MariaDB[market]>createtableuserprofile(idint(11)notnullauto_increment,namevarchar(50)notnulldefault'',sexint(1)notnulldefault'0',primarykey(id))ENGINE=innodb;#创建userprofile数据表，指定使用innodb引擎
QueryOK,0rowsaffected(0.07sec)

MariaDB[market]>createtableuser_order(o_idint(11)auto_increment,u_idint(11)default'0',usernamevarchar(50),moneyint(11),primarykey(o_id),index(u_id),foreignkeyorder_f_key(u_id)referencesuserprofile(id)ondeletecascadeonupdatecascade);#创建user_order数据表，同时为user_order表的u_id字段做外键约束，绑定userprofile表的id字段
QueryOK,0rowsaffected(0.04sec)

MariaDB[market]>insertintouserprofile(name,sex)values('HA',1),('LB',2),('HPC',1);#向userprofile数据表插入三条记录
QueryOK,3rowsaffected(0.01sec)
Records:3Duplicates:0Warnings:0

MariaDB[market]>select*fromuserprofile;#查询userprofile数据表的所有记录
+----+------+-----+
|id|name|sex|
+----+------+-----+
|1|HA|1|
|2|LB|2|
|3|HPC|1|
+----+------+-----+
3rowsinset(0.00sec)

MariaDB[market]>insertintouser_order(u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256);#向user_order数据表插入三条记录
QueryOK,3rowsaffected(0.02sec)
Records:3Duplicates:0Warnings:0

MariaDB[market]>select*fromuser_order;#查询user_order数据表的所有记录
+------+------+----------+-------+
|o_id|u_id|username|money|
+------+------+----------+-------+
|1|1|HA|234|
|2|2|LB|146|
|3|3|HPC|256|
+------+------+----------+-------+
3rowsinset(0.00sec)

MariaDB[market]>selectid,name,sex,money,o_idfromuserprofile,user_orderwhereid=u_id;#联表查询
+----+------+-----+-------+------+
|id|name|sex|money|o_id|
+----+------+-----+-------+------+
|1|HA|1|234|1|
|2|LB|2|146|2|
|3|HPC|1|256|3|
+----+------+-----+-------+------+
3rowsinset(0.03sec)

2.1.2测试级联删除

MariaDB[market]>deletefromuserprofilewhereid=1;#删除user表中id为1的数据
QueryOK,1rowaffected(0.01sec)

MariaDB[market]>selectid,name,sex,money,o_idfromuserprofile,user_orderwhereid=u_id;
+----+------+-----+-------+------+
|id|name|sex|money|o_id|
+----+------+-----+-------+------+
|2|LB|2|146|2|
|3|HPC|1|256|3|
+----+------+-----+-------+------+
2rowsinset(0.00sec)

MariaDB[market]>select*fromuser_order;#查看order表的数据
+------+------+----------+-------+
|o_id|u_id|username|money|
+------+------+----------+-------+
|2|2|LB|146|
|3|3|HPC|256|
+------+------+----------+-------+
3rowsinset(0.00sec)

2.1.3测试级联更新

更新数据之前的状态

MariaDB[market]>select*fromuserprofile;#查看userprofile表的数据
+----+------+-----+
|id|name|sex|
+----+------+-----+
|2|LB|2|
|3|HPC|1|
+----+------+-----+
3rowsinset(0.00sec)

MariaDB[market]>select*fromuser_order;#查看order表的数据
+------+------+----------+-------+
|o_id|u_id|username|money|
+------+------+----------+-------+
|2|2|LB|146|
|3|3|HPC|256|
+------+------+----------+-------+
3rowsinset(0.00sec)

更新数据

MariaDB[market]>updateuserprofilesetid=6whereid=2;#把userprofile数据表中id为2的用户改为id为6
QueryOK,1rowaffected(0.02sec)
Rowsmatched:1Changed:1Warnings:0

更新数据后的状态

MariaDB[market]>selectid,name,sex,money,o_idfromuserprofile,user_orderwhereid=u_id;#联表查询，可以看出表中已经没有id为2的用户了
+----+------+-----+-------+------+
|id|name|sex|money|o_id|
+----+------+-----+-------+------+
|6|LB|2|146|2|
|3|HPC|1|256|3|
+----+------+-----+-------+------+
2rowsinset(0.00sec)

MariaDB[market]>select*fromuserprofile;#查看userprofile表的数据，id只剩下3和6
+----+------+-----+
|id|name|sex|
+----+------+-----+
|3|HPC|1|
|6|LB|2|
+----+------+-----+
2rowsinset(0.00sec)

MariaDB[market]>select*fromuser_order;#查看user_order表的数据,u_id也改为6
+------+------+----------+-------+
|o_id|u_id|username|money|
+------+------+----------+-------+
|2|6|LB|146|
|3|3|HPC|256|
+------+------+----------+-------+
2rowsinset(0.00sec)

2.1.4测试数据完整性

MariaDB[market]>insertintouser_order(u_id,username,money)values(5,"XJ",345);#单独向user_order数据表中插入数据，插入数据失败
ERROR1452(23000):Cannotaddorupdateachildrow:aforeignkeyconstraintfails(`market`.`user_order`,CONSTRAINT`user_order_ibfk_1`FOREIGNKEY(`u_id`)REFERENCES`userprofile`(`id`)ONDELETECASCADEONUPDATECASCADE)

在上面的例子中，user_order表的外键约束，user_order表受userprofile表的约束

在user_order里面插入一条数据u_id为5用户，在userprofile表里面根本没有，所以插入数据失败

先向userprofile表中插入记录，再向user_order表中插入记录就可以了

MariaDB[market]>insertintouserprofilevalues(5,"XJ",1);#先向userprofile数据表中插入id为5的记录，插入数据成功
QueryOK,1rowaffected(0.01sec)

MariaDB[market]>insertintouser_order(u_id,username,money)values(5,"XJ",345);#再向user_order数据表中插入数据,成功
QueryOK,1rowaffected(0.00sec)

MariaDB[market]>select*fromuserprofile;#查询userprofile数据表中的所有记录
+----+------+-----+
|id|name|sex|
+----+------+-----+
|3|HPC|1|
|5|XJ|1|
|6|LB|2|
+----+------+-----+
3rowsinset(0.00sec)

MariaDB[market]>select*fromuser_order;#查询user_order数据表中的所有记录
+------+------+----------+-------+
|o_id|u_id|username|money|
+------+------+----------+-------+
|2|6|LB|146|
|3|3|HPC|256|
|5|5|XJ|345|
+------+------+----------+-------+
3rowsinset(0.01sec)

2.2方法二：通过altertable创建外键和级联更新，级联删除

语法：

altertable数据表名称add
[constraint[约束名称]]foreignkey(外键字段,..)references数据表(参照字段,...)
[onupdatecascade|setnull|noaction]
[ondeletecascade|setnull|noaction]
)

例子：

MariaDB[market]>createtableuser_order1(o_idint(11)auto_increment,u_idint(11)default"0",usernamevarchar(50),moneyint(11),primarykey(o_id),index(u_id));#创建user_order1数据表，创建表时不使用外键约束
QueryOK,0rowsaffected(0.11sec)

MariaDB[market]>showcreatetableuser_order1;#查看user_order1数据表的创建信息，没有外键约束
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table|CreateTable|
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|user_order1|CREATETABLE`user_order1`(
`o_id`int(11)NOTNULLAUTO_INCREMENT,
`u_id`int(11)DEFAULT'0',
`username`varchar(50)COLLATEutf8_unicode_ciDEFAULTNULL,
`money`int(11)DEFAULTNULL,
PRIMARYKEY(`o_id`),
KEY`u_id`(`u_id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_unicode_ci|
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset(0.01sec)

MariaDB[market]>altertableuser_order1addforeignkey(u_id)referencesuserprofile(id)ondeletecascadeonupdatecascade;#使用alter修改user_order1数据表，为user_order1数据表添加外键约束
QueryOK,0rowsaffected(0.05sec)
Records:0Duplicates:0Warnings:0

MariaDB[market]>showcreatetableuser_order1;#查看user_order1数据表的创建信息，已经添加了外键约束
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table|CreateTable|
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|user_order1|CREATETABLE`user_order1`(
`o_id`int(11)NOTNULLAUTO_INCREMENT,
`u_id`int(11)DEFAULT'0',
`username`varchar(50)COLLATEutf8_unicode_ciDEFAULTNULL,
`money`int(11)DEFAULTNULL,
PRIMARYKEY(`o_id`),
KEY`u_id`(`u_id`),
CONSTRAINT`user_order1_ibfk_1`FOREIGNKEY(`u_id`)REFERENCES`userprofile`(`id`)ONDELETECASCADEONUPDATECASCADE
)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_unicode_ci|
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset(0.00sec)

3.删除外键

语法

altertable数据表名称dropforeignkey约束（外键）名称

例子：

MariaDB[market]>showcreatetableuser_order1;#查看user_order1数据表的创建信息，包含外键约束
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table|CreateTable|
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|user_order1|CREATETABLE`user_order1`(
`o_id`int(11)NOTNULLAUTO_INCREMENT,
`u_id`int(11)DEFAULT'0',
`username`varchar(50)COLLATEutf8_unicode_ciDEFAULTNULL,
`money`int(11)DEFAULTNULL,
PRIMARYKEY(`o_id`),
KEY`u_id`(`u_id`),
CONSTRAINT`user_order1_ibfk_1`FOREIGNKEY(`u_id`)REFERENCES`userprofile`(`id`)ONDELETECASCADEONUPDATECASCADE
)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_unicode_ci|
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset(0.00sec)

MariaDB[market]>altertableuser_order1dropforeignkeyuser_order1_ibfk_1;#为user_order1数据表删除外键约束，外键名称必须与从`showcreatetableuser_order1`语句中查到的相同
QueryOK,0rowsaffected(0.05sec)
Records:0Duplicates:0Warnings:0

MariaDB[market]>showcreatetableuser_order1;#查看user_order1数据表的创建信息，外键约束已经被删除了
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|Table|CreateTable|
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|user_order1|CREATETABLE`user_order1`(
`o_id`int(11)NOTNULLAUTO_INCREMENT,
`u_id`int(11)DEFAULT'0',
`username`varchar(50)COLLATEutf8_unicode_ciDEFAULTNULL,
`money`int(11)DEFAULTNULL,
PRIMARYKEY(`o_id`),
KEY`u_id`(`u_id`)
)ENGINE=InnoDBDEFAULTCHARSET=utf8COLLATE=utf8_unicode_ci|
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1rowinset(0.00sec)

4.使用外键约束的条件

要想外键创建成功，必须满足以下4个条件：

1、确保参照的表和字段存在。
2、组成外键的字段被索引。
3、必须使用type指定存储引擎为：innodb.
4、外键字段和关联字段，数据类型必须一致。

5.使用外键约束需要的注意事项

1.ondeletecascade onupdatecascade添加级联删除和更新：
2.确保参照的表userprofile中id字段存在。
3.确保组成外键的字段u_id被索引
4.必须使用type指定存储引擎为：innodb。
5.外键字段和关联字段，数据类型必须一致。

总结

以上就是这篇文章的全部内容了，希望本文的内容对大家的学习或者工作具有一定的参考学习价值，如果有疑问大家可以留言交流，谢谢大家对毛票票的支持。

MariaDB数据库的外键约束实例详解

热门推荐

随机推荐