分类导航

Mariadb 发布时间：2019-11-06 发布网站：大佬教程 code.js-code.com

大佬教程收集整理的这篇文章主要介绍了MariaDB数据库的外键约束实例详解，大佬教程大佬觉得挺不错的，现在分享给大家，也给大家做个参考。

外键

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

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

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

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

1.什么是外键约束

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

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

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

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

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

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

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

2.创建外键约束

2.1 方法一：通过create table创建外键

语法：

create table 数据表名称( ...,[CONSTraiNT [约束名称]] FOREIGN KEY [外键字段] REFERENCES [外键表名](外键字段，外键字段2…..) [ON deletE CASCADE ] [ON updatE CASCADE ] )

参数的解释：

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

精简化后的语法：

2.1.1 插入测试数据

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

create database market; # 创建market数据库 Query OK,1 row affected (0.00 seC) @H_568_0@mariaDB [book]> use market; # 使用market数据库
Database changed

@H_568_0@mariaDB [market]> create table userprofile(id int(11) not null auto_increment,name varchar(50) NOT NULL default '',sex int(1) not null default '0',@R_821_1@R_489_11287@@))ENGINE=innodb; # 创建userprofile数据表，指定使用innodb引擎
Query OK,0 rows affected (0.07 seC)

@H_568_0@mariaDB [market]> create table @R_@R_489_11287@_9004@order(o_id int(11) auto_increment,u_id int(11) default '0',username VARCHAR(50),money int(11),priMary key(o_id),index(u_id),foreign key order_f_key(u_id) references userprofile(id) on delete cascade on update cascadE); # 创建@R_@R_489_11287@_9004@order数据表，同时为@R_@R_489_11287@_9004@order表的u_id字段做外键约束，绑定userprofile表的id字段
Query OK,0 rows affected (0.04 seC)

@H_568_0@mariaDB [market]> insert into userprofile(name,seX)values('HA',1),('LB',2),('HPC',1); # 向userprofile数据表插入三条记录
Query OK,3 rows affected (0.01 seC)
Records: 3 Duplicates: 0 Warnings: 0

@H_568_0@mariaDB [market]> SELEct * from userprofile; # 查询userprofile数据表的所有记录
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 1 | HA | 1 |
| 2 | LB | 2 |
| 3 | HPC | 1 |
+----+------+-----+
3 rows in set (0.00 seC)

@H_568_0@mariaDB [market]> insert into @R_@R_489_11287@_9004@order(u_id,username,money)values(1,'HA',234),(2,'LB',146),(3,'HPC',256); # 向@R_@R_489_11287@_9004@order数据表插入三条记录
Query OK,3 rows affected (0.02 seC)
Records: 3 Duplicates: 0 Warnings: 0

@H_568_0@mariaDB [market]> SELEct * from @R_@R_489_11287@_9004@order; # 查询@R_@R_489_11287@_9004@order数据表的所有记录
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 1 | 1 | HA | 234 |
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
3 rows in set (0.00 seC)

@H_568_0@mariaDB [market]> SELEct id,name,sex,money,o_id from userprofile,@R_@R_489_11287@_9004@order where id=u_id; # 联表查询
+----+------+-----+-------+------+
| id | name | sex | money | o_id |
+----+------+-----+-------+------+
| 1 | HA | 1 | 234 | 1 |
| 2 | LB | 2 | 146 | 2 |
| 3 | HPC | 1 | 256 | 3 |
+----+------+-----+-------+------+
3 rows in set (0.03 seC)

2.1.2 测试级联删除

delete from userprofile where id=1; # 删除user表中id为1的数据 Query OK,1 row affected (0.01 seC) @H_568_0@mariaDB [market]> SELEct id,@R_@R_489_11287@_9004@order where id=u_id;
+----+------+-----+-------+------+
| id | name | sex | money | o_id |
+----+------+-----+-------+------+
| 2 | LB | 2 | 146 | 2 |
| 3 | HPC | 1 | 256 | 3 |
+----+------+-----+-------+------+
2 rows in set (0.00 seC)

@H_568_0@mariaDB [market]> SELEct * from @R_@R_489_11287@_9004@order; # 查看order表的数据
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
3 rows in set (0.00 seC)

2.1.3 测试级联更新

更新数据之前的状态

SELEct * from userprofile; # 查看userprofile表的数据 +----+------+-----+ | id | name | sex | +----+------+-----+ | 2 | LB | 2 | | 3 | HPC | 1 | +----+------+-----+ 3 rows in set (0.00 seC) @H_568_0@mariaDB [market]> SELEct * from @R_@R_489_11287@_9004@order; # 查看order表的数据
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 2 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
3 rows in set (0.00 seC)

更新数据

update userprofile set id=6 where id=2; # 把userprofile数据表中id为2的用户改为id为6 Query OK,1 row affected (0.02 seC) Rows matched: 1 Changed: 1 Warnings: 0

更新数据后的状态

SELEct id,@R_@R_489_11287@_9004@order where id=u_id; # 联表查询，可以看出表中已经没有id为2的用户了 +----+------+-----+-------+------+ | id | name | sex | money | o_id | +----+------+-----+-------+------+ | 6 | LB | 2 | 146 | 2 | | 3 | HPC | 1 | 256 | 3 | +----+------+-----+-------+------+ 2 rows in set (0.00 seC) @H_568_0@mariaDB [market]> SELEct * from userprofile; # 查看userprofile表的数据，id只剩下3和6
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 3 | HPC | 1 |
| 6 | LB | 2 |
+----+------+-----+
2 rows in set (0.00 seC)

@H_568_0@mariaDB [market]> SELEct * from @R_@R_489_11287@_9004@order; # 查看@R_@R_489_11287@_9004@order表的数据,u_id也改为6
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 6 | LB | 146 |
| 3 | 3 | HPC | 256 |
+------+------+----------+-------+
2 rows in set (0.00 seC)

2.1.4 测试数据完整性

insert into @R_@R_489_11287@_9004@order(u_id,money)values(5,"XJ",345); # 单独向@R_@R_489_11287@_9004@order数据表中插入数据，插入数据失败 ERROR 1452 (23000): CAnnot add or update a child row: a foreign key consTraint fails (`market`.`@R_@R_489_11287@_9004@order`,CONSTraiNT `@R_@R_489_11287@_9004@order_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userprofile` (`id`) ON deletE CASCADE ON updatE CASCADE)

在上面的例子中，@R_@R_489_11287@_9004@order表的外键约束，@R_@R_489_11287@_9004@order表受userprofile表的约束

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

先向userprofile表中插入记录，再向@R_@R_489_11287@_9004@order表中插入记录就可以了

insert into userprofile values(5,1); # 先向userprofile数据表中插入id为5的记录，插入数据成功 Query OK,1 row affected (0.01 seC) @H_568_0@mariaDB [market]> insert into @R_@R_489_11287@_9004@order(u_id,money) VALUES(5,345); # 再向@R_@R_489_11287@_9004@order数据表中插入数据,成功
Query OK,1 row affected (0.00 seC)

@H_568_0@mariaDB [market]> SELEct * from userprofile; # 查询userprofile数据表中的所有记录
+----+------+-----+
| id | name | sex |
+----+------+-----+
| 3 | HPC | 1 |
| 5 | XJ | 1 |
| 6 | LB | 2 |
+----+------+-----+
3 rows in set (0.00 seC)

@H_568_0@mariaDB [market]> SELEct * from @R_@R_489_11287@_9004@order; # 查询@R_@R_489_11287@_9004@order数据表中的所有记录
+------+------+----------+-------+
| o_id | u_id | username | money |
+------+------+----------+-------+
| 2 | 6 | LB | 146 |
| 3 | 3 | HPC | 256 |
| 5 | 5 | XJ | 345 |
+------+------+----------+-------+
3 rows in set (0.01 seC)

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

语法：

alter table 数据表名称 add [consTraint [约束名称] ] foreign key (外键字段,..) references 数据表(参照字段,...) [on update cascade|set null|no action] [on delete cascade|set null|no action] )

例子：

create table @R_@R_489_11287@_9004@order1(o_id int(11) auto_increment,u_id int(11) default "0",index(u_id)); # 创建@R_@R_489_11287@_9004@order1数据表，创建表时不使用外键约束 Query OK,0 rows affected (0.11 seC) @H_568_0@mariaDB [market]> show create table @R_@R_489_11287@_9004@order1; # 查看@R_@R_489_11287@_9004@order1数据表的创建信息，没有外键约束
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | create table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @R_@R_489_11287@_9004@order1 | create table @R_@R_489_11287@_9004@order1 (
o_id int(11) NOT NULL AUTO_INCREMENT,u_id int(11) DEFAULT '0',username varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,@H_870_253@money int(11) DEFAULT NULL,PRIMARY KEY (o_id),KEY u_id (u_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 seC)

@H_568_0@mariaDB [market]> alter table @R_@R_489_11287@_9004@order1 add foreign key(u_id) references userprofile(id) on delete cascade on update cascade; # 使用alter修改@R_@R_489_11287@_9004@order1数据表，为@R_@R_489_11287@_9004@order1数据表添加外键约束
Query OK,0 rows affected (0.05 seC)
Records: 0 Duplicates: 0 Warnings: 0

@H_568_0@mariaDB [market]> show create table @R_@R_489_11287@_9004@order1; # 查看@R_@R_489_11287@_9004@order1数据表的创建信息，已经添加了外键约束
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | create table |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @R_@R_489_11287@_9004@order1 | create table @R_@R_489_11287@_9004@order1 (
o_id int(11) NOT NULL AUTO_INCREMENT,KEY u_id (u_id),CONSTraiNT @R_@R_489_11287@_9004@order1_ibfk_1 FOREIGN KEY (u_id) REFERENCES userprofile (id) ON deletE CASCADE ON updatE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 seC)

3.删除外键

语法

alter table 数据表名称 drop foreign key 约束（外键）名称

例子：

show create table @R_@R_489_11287@_9004@order1; # 查看@R_@R_489_11287@_9004@order1数据表的创建信息，包含外键约束 +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | create table | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | @R_@R_489_11287@_9004@order1 | create table `@R_@R_489_11287@_9004@order1` ( `o_id` int(11) NOT NULL AUTO_INCREMENT,CONSTraiNT `@R_@R_489_11287@_9004@order1_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userprofile` (`id`) ON deletE CASCADE ON updatE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci | +-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 seC) @H_568_0@mariaDB [market]> alter table @R_@R_489_11287@_9004@order1 drop foreign key @R_@R_489_11287@_9004@order1_ibfk_1; # 为@R_@R_489_11287@_9004@order1数据表删除外键约束，外键名称必须与从show create table @R_@R_489_11287@_9004@order1语句中查到的相同
Query OK,0 rows affected (0.05 seC)
Records: 0 Duplicates: 0 Warnings: 0

@H_568_0@mariaDB [market]> show create table @R_@R_489_11287@_9004@order1; # 查看@R_@R_489_11287@_9004@order1数据表的创建信息，外键约束已经被删除了
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | create table |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| @R_@R_489_11287@_9004@order1 | create table @R_@R_489_11287@_9004@order1 (
o_id int(11) NOT NULL AUTO_INCREMENT,KEY u_id (u_id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 seC)

4.使用外键约束的条件

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

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

总结

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

大佬总结

以上是大佬教程为你收集整理的MariaDB数据库的外键约束实例详解全部内容，希望文章能够帮你解决MariaDB数据库的外键约束实例详解所遇到的程序开发问题。

如果觉得大佬教程网站内容还不错，欢迎将大佬教程推荐给程序员好友。

本图文内容来源于网友网络收集整理提供，作为学习参考使用，版权属于原作者。
如您有任何意见或建议可联系处理。小编QQ：384754419，请注明来意。

标签：

上一篇: Node.js Sequelize如何实现数据库... 下一篇:Exchange在接收连接器上启用匿名...

猜你在找的Mariadb相关文章

浅谈MySQL和mariadb区别 2022-05-23
centos 7下安装mysql（MariaDB）的教程 2019-11-06
CentOS 7中成功安装MariaDB的方法教程 2019-11-06
Windows Server 2016 服务器配置指南之 MariaDB数据库环境搭建方法 2019-11-06
Window7安装MariaDB数据库及系统初始化操作分析 2019-11-06
我是如何用2个Unix命令给MariaDB SQL提速的 2019-11-06
Exchange在接收连接器上启用匿名中继的方法 2019-11-06
MariaDB数据库的外键约束实例详解 2019-11-06
Node.js Sequelize如何实现数据库的读写分离 2019-11-06
在Mac OS上编译安装Nginx+PHP+MariaDB开发环境的教程 2019-11-06