已超过 507 天未更新，某些文章具有时效性，若有错误或已失效，请反馈到 greenhat2333@gamil.com

外键与级联更新删除

之前没用过外键与级联更新，所以总结一下，用的数据库版本：

mysql Ver 8.0.15 for linux-glibc2.12 on x86_64 (MySQL Community Server - GPL)

外键

定义

外键（foreign key） 是用于建立和加强两个表数据之间的链接的一列或多列。外键约束主要用来维护两个表之间数据的一致性。简言之，表的外键就是另一表的主键，外键将两表联系起来。一般情况下，要删除一张表中的主键必须首先要确保其它表中的没有相同外键（即该表中的主键没有一个外键和它相关联）。

MySQL 有两种常用的引擎类型：MyISAM 和 InnoDB。目前只有 InnoDB 引擎类型支持外键约束。

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；

外键术语

  • 外键约束
  • 外键字段：某个字段添加外键约束之后，该字段称为外键字段
  • 外键值：外键字段中的每一个数据都是外键值

具体实例

什么？看完定义还不知道是什么？那就试试看下面咯

当我们用主键唯一标识记录时，我们就可以在 students 表中确定任意一个学生的记录：

id name
1 小明
2 小红

我们还可以在 classes 表中确定任意一个班级记录：

id name
1 一班
2 二班

但是我们如何确定 students 表的一条记录，例如，id=1 的小明，属于哪个班级呢？

由于一个班级可以有多个学生，在关系模型中，这两个表的关系可以称为 “一对多”，即一个 classes 的记录可以对应多个 students 表的记录。

为了表达这种一对多的关系，我们需要在 students 表中加入一列 class_id，让它的值与 classes 表的某条记录相对应：

id class_id name
1 1 小明
2 1 小红
5 2 小白

这样，我们就可以根据 class_id 这个列直接定位出一个 students 表的记录应该对应到 classes 的哪条记录。

例如：

  • 小明的 class_id 是 1，因此，对应的 classes 表的记录是 id=1 的一班；
  • 小红的 class_id 是 1，因此，对应的 classes 表的记录是 id=1 的一班；
  • 小白的 class_id 是 2，因此，对应的 classes 表的记录是 id=2 的二班。

在 students 表中，通过 class_id 的字段，可以把数据与另一张表关联起来，这种列称为外键

结论：为了保证 students 表中的 class_id 字段中的数据必须来自于 classes 表中 id 字段中的数据，有必要给 students 表中的 class_id 字段添加外键约束，class_id 字段被称为外键字段，该字段中的 1 2 被称为外键值。class_id 这里是一个单一外键字段

注意事项与主从表

  • 外键值可以为 null

  • 外键字段去引用一张表的某个字段的时候，被引用的字段必须具有 unique 约束。

  • 有了外键引用之后，表分为父表和子表

    当两个表建立一对多关系的时候，” 一” 的那一端是父表，” 多” 的那一端是子表。

    父表设置一个主键
    子表设置一个外键
    外键与主键相关联
    B 表引用 A 表的字段作为外键，那么 A 表是主表，B 表是从表。

    以上父表是：classes 表
    字表是：students 表
    创建表：先创建父表，再创建子表；
    删除数据：先删除子表中的数据，再删除父表中的数据；
    插入数据：先插入父表中的数据，再插入子表中的数据。

添加外键

建立外键关系的对应列必须建立了索引

Alter 方式

外键并不是通过列名实现的，而是通过定义外键约束实现的：

sql
1
2
3
4
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id);

其中：

  • 外键约束的名称 fk_class_id 可以任意，不用名字的话可以去掉 CONSTRAINT fk_class_id
  • FOREIGN KEY (class_id) 指定了 class_id 作为外键
  • REFERENCES classes (id) 指定了这个外键将关联到 classes 表的 id 列（即 classes 表的主键）。

Create Table 方式

sql
1
2
3
4
5
6
7
8
CREATE TABLE students(
	id INT,
    class_id INT,
    name VARCHAR(10),
    INDEX(class_id),
    PRIMARY KEY(id),
    FOREIGN KEY (class_id) REFERENCES classes (id)
)

删除外键

sql
1
2
ALTER TABLE students
DROP FOREIGN KEY fk_class_id;

注意：删除外键约束并没有删除外键这一列。删除列是通过 DROP COLUMN ... 实现的。

分类

单一外键

给一个字段添加外键约束，如上面的样例

复合外键

给多个字段联合添加一个外键约束

多对多

通过一个表的外键关联到另一个表，我们可以定义出一对多关系。有些时候，还需要定义 “多对多” 关系。例如，一个老师可以对应多个班级，一个班级也可以对应多个老师，因此，班级表和老师表存在多对多关系。

多对多关系实际上是通过两个一对多关系实现的，即通过一个中间表，关联两个一对多关系，就形成了多对多关系：

teachers 表

id name
1 张老师
2 王老师
3 李老师
4 赵老师

classes 表

id name
1 一班
2 二班

teacher_class 表

中间表 teacher_class 关联两个一对多关系：

id teacher_id class_id
1 1 1
2 1 2
3 2 1
4 2 2
5 3 1
6 4 2

关系

通过中间表 teacher_class 可知 teachers 到 classes 的关系：

  • id=1 的张老师对应 id=1,2 的一班和二班；
  • id=2 的王老师对应 id=1,2 的一班和二班；
  • id=3 的李老师对应 id=1 的一班；
  • id=4 的赵老师对应 id=2 的二班。

同理可知 classes 到 teachers 的关系：

  • id=1 的一班对应 id=1,2,3 的张老师、王老师和李老师；
  • id=2 的二班对应 id=1,2,4 的张老师、王老师和赵老师；

因此，通过中间表，我们就定义了一个 “多对多” 关系。

多对多关系的外键则是在中间表中的两个字段上设置两个外键

一对一

一对一关系是指，一个表的记录对应到另一个表的唯一一个记录。

例如，students 表的每个学生可以有自己的联系方式，如果把联系方式存入另一个表 contacts，我们就可以得到一个 “一对一” 关系：

id student_id mobile
1 1 135xxxx6300
2 2 138xxxx2209
3 5 139xxxx8086

有细心的童鞋会问，既然是一对一关系，那为啥不给 students 表增加一个 mobile 列，这样就能合二为一了？

如果业务允许，完全可以把两个表合为一个表。但是，有些时候，如果某个学生没有手机号，那么，contacts 表就不存在对应的记录。实际上，一对一关系准确地说，是 contacts 表一对一对应 students 表。

还有一些应用会把一个大表拆成两个一对一的表，目的是把经常读取和不经常读取的字段分开，以获得更高的性能。例如，把一个大的用户表分拆为用户基本信息表 user_info 和用户详细信息表 user_profiles，大部分时候，只需要查询 user_info 表，并不需要查询 user_profiles 表，这样就提高了查询速度。

为什么会提高查询速度？

  • 学生的联系信息不完整，有的没有手机号，在一个表格里就会有 Null 存在。而放在另一个表格里，没有手机号的学生就没有记录。避免 null，性能更快
  • 一般来说重要的核心信息是大家都明确有的一定要收集到的，比如名字和身份号。一些不核心的信息会根据情况表现不完整，且平常对这些信息的使用也不频繁，因此放在另一个表格中就避免每次访问数据库都要连带操作这些信息。

级联操作

在删除父表中的数据的时候，级联删除子表中的数据 on delete cascade

在更新父表中的数据时候，级联更新子表中的数据 on update cascade

级联操作在外键约束后面添加

sql
1
2
3
4
5
6
7
8
9
CREATE TABLE students(
	id INT,
    class_id INT,
    name VARCHAR(10),
    INDEX(class_id),
    PRIMARY KEY(id),
    FOREIGN KEY (class_id) REFERENCES classes (id)
    ON DELETE CASCADE ON UPDATE CASCADE
);
sql
1
2
3
4
5
ALTER TABLE students
ADD CONSTRAINT fk_class_id
FOREIGN KEY (class_id)
REFERENCES classes (id)
ON DELETE CASCADE ON UPDATE CASCADE;

外键的性能问题

  • 数据库需要维护外键的内部管理
  • 外键等于把数据的一致性事务实现，全部交给数据库服务器完成
  • 有了外键，当做一些涉及外键字段的增，删，更新操作之后，需要触发相关操作去检查，而不得不消耗资源
  • 外键还会因为需要请求对其他表内部加锁而容易出现死锁情况

是否使用外键

外键是否采用看业务应用场景，以及开发成本的，大致列下什么时候适合，什么时候不适合使用：

不适合

互联网行业应用不推荐使用外键： 用户量大，并发度高，为此数据库服务器很容易成为性能瓶颈，尤其受 IO 能力限制，且不能轻易地水平扩展；若是把数据一致性的控制放到事务中，也即让应用服务器承担此部分的压力，而引用服务器一般都是可以做到轻松地水平的伸缩；

适合

  • 软件应用的人数有限，换句话说是可控的
  • 数据库服务器的数据量也一般不会超大，且活跃数据有限

综合上述 2 句话描述，也即数据库服务器的性能不是问题，所以不用过多考虑性能的问题；另外，使用外键可以降低开发成本，借助数据库产品自身的触发器可以实现表与关联表之间的数据一致性和更新；最后一点，使用外键的方式，还可以做到开发人员和数据库设计人员的分工，可以为程序员承担更多的工作量；

参考：

MySQL 的几个概念：主键，外键，索引，唯一索引 - 秋天的童话 - 51CTO 博客

外键 - 廖雪峰的官方网站

MySql— 约束和级联操作 - 简书

mysql 之外键约束（级联操作等） 父表子表 - 飞翔的大象 - SegmentFault 思否

大家设计数据库时使用外键吗？ - 知乎