• 企业400电话
  • 微网小程序
  • AI电话机器人
  • 电商代运营

    • 全 部 栏 目

    企业400电话 网络优化推广 AI电话机器人 呼叫中心 网站建设 商标✡知产 微网小程序 电商运营 彩铃•短信 增值拓展业务
    MariaDB数据库的外键约束实例详解

    外键

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

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

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

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

    1.什么是外键约束

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

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

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

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

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

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

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

    2.创建外键约束

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

    语法：

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

    参数的解释：

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

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

    精简化后的语法：

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

    2.1.1 插入测试数据

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

    MariaDB [book]> create database market;  # 创建market数据库
Query OK, 1 row affected (0.00 sec)

MariaDB [book]> use market;    # 使用market数据库
Database changed

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', primary key(id))ENGINE=innodb; # 创建userprofile数据表，指定使用innodb引擎
Query OK, 0 rows affected (0.07 sec)

MariaDB [market]> create table user_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);  # 创建user_order数据表，同时为user_order表的u_id字段做外键约束，绑定userprofile表的id字段
Query OK, 0 rows affected (0.04 sec)

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

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)

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

MariaDB [market]> select * from user_order;  # 查询user_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)

MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_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 测试级联删除

    MariaDB [market]> delete from userprofile where id=1; # 删除user表中id为1的数据
Query OK, 1 row affected (0.01 sec)

MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_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)

MariaDB [market]> select * from user_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 测试级联更新

    更新数据之前的状态

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

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

    更新数据

    MariaDB [market]> 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

    更新数据后的状态

    MariaDB [market]> select id,name,sex,money,o_id from userprofile,user_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)

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)

MariaDB [market]> select * from user_order;   # 查看user_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 测试数据完整性

    MariaDB [market]> insert into user_order(u_id,username,money)values(5,"XJ",345); # 单独向user_order数据表中插入数据，插入数据失败
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`market`.`user_order`, CONSTRAINT `user_order_ibfk_1` FOREIGN KEY (`u_id`) REFERENCES `userprofile` (`id`) ON DELETE CASCADE ON UPDATE CASCADE)

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

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

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

    MariaDB [market]> insert into userprofile values(5,"XJ",1);  # 先向userprofile数据表中插入id为5的记录，插入数据成功
Query OK, 1 row affected (0.01 sec)

MariaDB [market]> insert into user_order(u_id,username,money) values(5,"XJ",345); # 再向user_order数据表中插入数据,成功
Query OK, 1 row affected (0.00 sec)

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)

MariaDB [market]> select * from user_order;   # 查询user_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]
)

    例子：

    MariaDB [market]> create table user_order1(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));  # 创建user_order1数据表，创建表时不使用外键约束
Query OK, 0 rows affected (0.11 sec)

MariaDB [market]> show create table user_order1;  # 查看user_order1数据表的创建信息，没有外键约束
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                     |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_order1 | CREATE TABLE `user_order1` (
 `o_id` int(11) NOT NULL AUTO_INCREMENT,
 `u_id` int(11) DEFAULT '0',
 `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `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)

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

MariaDB [market]> show create table user_order1;  # 查看user_order1数据表的创建信息，已经添加了外键约束
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                    |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_order1 | CREATE TABLE `user_order1` (
 `o_id` int(11) NOT NULL AUTO_INCREMENT,
 `u_id` int(11) DEFAULT '0',
 `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `money` int(11) DEFAULT NULL,
 PRIMARY KEY (`o_id`),
 KEY `u_id` (`u_id`),
 CONSTRAINT `user_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 约束（外键）名称

    例子：

    MariaDB [market]> show create table user_order1;  # 查看user_order1数据表的创建信息，包含外键约束
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                    |
+-------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_order1 | CREATE TABLE `user_order1` (
 `o_id` int(11) NOT NULL AUTO_INCREMENT,
 `u_id` int(11) DEFAULT '0',
 `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `money` int(11) DEFAULT NULL,
 PRIMARY KEY (`o_id`),
 KEY `u_id` (`u_id`),
 CONSTRAINT `user_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)

MariaDB [market]> alter table user_order1 drop foreign key user_order1_ibfk_1;  # 为user_order1数据表删除外键约束，外键名称必须与从`show create table user_order1`语句中查到的相同
Query OK, 0 rows affected (0.05 sec)  
Records: 0 Duplicates: 0 Warnings: 0

MariaDB [market]> show create table user_order1;  # 查看user_order1数据表的创建信息，外键约束已经被删除了
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                     |
+-------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user_order1 | CREATE TABLE `user_order1` (
 `o_id` int(11) NOT NULL AUTO_INCREMENT,
 `u_id` int(11) DEFAULT '0',
 `username` varchar(50) COLLATE utf8_unicode_ci DEFAULT NULL,
 `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.00 sec)

    4.使用外键约束的条件

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

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

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

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

    总结

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

    您可能感兴趣的文章:
    • 浅谈MySQL和mariadb区别
    • centos 7安装mysql5.5和安装 mariadb使用的命令
    • Centos7 下mysql重新启动MariaDB篇
    • Mac中MariaDB数据库的安装步骤
    • mariadb的主从复制、主主复制、半同步复制配置详解
    • CentOS安装和设置MariaDB的教程
    • 关于MariaDB安装问题小记(CMake Error at)
    • 记一次mariadb数据库无法连接
    • CentOS 7中成功安装MariaDB的方法教程
    • MariaDB性能调优工具mytop的使用详解
    上一篇：我是如何用2个Unix命令给MariaDB SQL提速的
    下一篇：Windows10系统下安装MariaDB 的教程图解
  • 相关文章
    • 

    © 2016-2020 巨人网络通讯 版权所有

    《增值电信业务经营许可证》 苏ICP备15040257号-8

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