详解MariaDB数据库的外键约束

本文涉及的产品
传统型负载均衡 CLB,每月750个小时 15LCU
网络型负载均衡 NLB,每月750个小时 15LCU
应用型负载均衡 ALB,每月750个小时 15LCU
简介: 1.什么是外键约束外键约束(foreign key)就是表与表之间的某种约定的关系,由于这种关系的存在,我们能够让表与表之间的数据,更加的完整,关连性更强。关于数据表的完整性和关连性,可以举个例子有二张表,一张是用户表,一张是订单表:1.如果我删除了用户表里的用户,那么订单表里面跟这个用户有关的数据,就成了无头数据了,不完整了。

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.外键字段和关联字段,数据类型必须一致。
相关实践学习
使用CloudLens观测ALB下的网站访问情况
通过本实验,您可搭建网站,并使用ALB进行负载均衡,同时使用CloudLens for ALB一键采集ALB日志,进行ALB 7层日志分析、秒级监控指标分析、基于AIOps的自动异常巡检等操作。
负载均衡入门与产品使用指南
负载均衡(Server Load Balancer)是对多台云服务器进行流量分发的负载均衡服务,可以通过流量分发扩展应用系统对外的服务能力,通过消除单点故障提升应用系统的可用性。 本课程主要介绍负载均衡的相关技术以及阿里云负载均衡产品的使用方法。
目录
相关文章
|
2月前
|
SQL 关系型数据库 MySQL
轻松入门MySQL:深入学习数据库表管理,创建、修改、约束、建议与性能优化(3)
轻松入门MySQL:深入学习数据库表管理,创建、修改、约束、建议与性能优化(3)
|
2月前
|
存储 关系型数据库 MySQL
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
MySQL数据库性能大揭秘:表设计优化的高效策略(优化数据类型、增加冗余字段、拆分表以及使用非空约束)
|
28天前
|
数据采集 关系型数据库 MySQL
MySQL数据库基础第三篇(约束)
MySQL数据库基础第三篇(约束)
|
1月前
|
SQL 关系型数据库 MySQL
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)一
29 5
|
1月前
|
数据库 数据库管理 索引
Liquibase中的约束与索引,让你的数据库管理如丝般顺滑
【Liquibase教程】数据库变更管理利器!学会添加主键、外键、检查约束和索引,提升开发效率。开源工具Liquibase帮你轻松控制数据库版本,确保数据完整性和一致性。示例代码教你如何在Liquibase中创建表并定义各种约束,让数据库管理更加高效。下次见!
Liquibase中的约束与索引,让你的数据库管理如丝般顺滑
|
2月前
|
存储 关系型数据库 数据库
关系型数据库的数据完整性约束
【5月更文挑战第12天】关系型数据库的数据完整性约束
47 2
|
1月前
|
SQL 存储 关系型数据库
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)二
MySQL数据库——基础篇总结(概述、SQL、函数、约束、多表查询、事务)二
30 0
|
1月前
|
存储 关系型数据库 MySQL
MySQL数据库——约束(概述、约束演示、外键约束、删除/更新行为)
MySQL数据库——约束(概述、约束演示、外键约束、删除/更新行为)
22 0
|
2月前
|
存储 分布式计算 大数据
数据库的约束
数据库的约束
19 2
|
2月前
|
SQL 关系型数据库 MySQL
MySQL数据库的约束+进阶版新增与查询-2
MySQL数据库的约束+进阶版新增与查询
69 1

推荐镜像

更多