关系型数据库的外键约束与关联

程序员文章站 2022-05-08 09:30:32

目录文章目录目录外键的作用外键的性能问题是否使用外键？外键的作用外键用于支持关系型数据库的 “参照完整性”，外键具有保持数据完整性和一致性的机制，对业务处理有着很好的校验作用。举例说明：假设 Table user 的 Column user.id 为主键（Primary key），Table profile 的 Column profile.uid 为主键。以 user 为主表、profile 为关联表、profile.uid 为外键（Foreign key）并将 user.id 作为参考（R...

外键约束

外键约束（Foreign key）是关系型数据库中的 Table 的一个特殊字段，经常与主键约束（Primary key）一起使用。对于两个具有关联关系的表而言，相关联字段中主键所在的表就是主表（父表），外键所在的表就是从表（子表）。外键约束可以保证引用的完整性（Referential Integrity）。

引用完整性是数据的属性，如果数据拥有该属性，那么数据中所有的引用都是合法的，在关系型数据库的上下文中，这就意味着关系型数据库中引用另一个表中的值必须存在。

简而言之，外键约束就是用来建立主表与从表的关联关系，为两个表的数据建立连接，约束两个表中数据的一致性和完整性。

NOTE：一个 Table 可以有一个或多个外键，外键可以为空值，若不为空值，则每一个外键的值必须等于主表中主键的某个值。

外键关联

所谓外键关联，即：B 存在外键 b_f_k，以 A 表的 a_k 作为参照（References）列，则 A 为主表，B 为从表。

若 A、B 关联了 on delete/update 等操作，则 A 中某记录的更新或删除会联动着 B 中外键与其关联对应的记录做更新或删除操作。
反之，B 怎样变 A 不必跟随变动，且 A 中必须事先存在 B 要插入的数据外键列的值，例如：B.bfk 作为外键参照 A.ak，则 B.bfk 插入的值必须是 A.ak 中已存在的。简而言之，就是若 B 有以 A 作为参照的外键，则 B 中的此字段的取值只能是 A 中存在的值。

外键的作用

外键用于支持关系型数据库的 “参照完整性”，外键具有保持数据完整性和一致性的机制，对业务处理有着很好的校验作用。

举例说明：假设 Table user 的 Column user.id 为主键（Primary key），Table profile 的 Column profile.uid 为主键。以 user 为主表、profile 为关联表、profile.uid 为外键（Foreign key）并将 user.id 作为参考（References），且联动了删除/更新操作（on delete/update cascade）。那么：

在 user 中删除 id 为 1 的记录，会联动删除 profile 中 uid 为 1 的记录。
在 user 中更新 id 为 1 的记录至 id 为 2，则 profile 中 uid 为 1 的记录也会被联动更新至 uid 为 2。

这样即保持了数据的完整性，也保证了数据的一致性。而且这个工作都是交由 RDBMS 内部实现的触发器来完成的，不需要额外的编码。

外键的性能问题

外键的使用往往会带来性能问题，因为：

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

是否使用外键？

因为外键具有性能问题，所以是否采用外键需要考虑业务应用场景，以及开发成本：

互联网行业应用不推荐使用外键：用户量大，并发度高。为此，数据库服务器很容易成为高并发访问的性能瓶颈，尤其受 I/O 能力限制，且不能轻易地水平扩展。此场景中，应该把数据一致性的实现放到业务逻辑中，让应用服务器来承担这部分的功能和压力，因为应用服务器可以轻松做到水平伸缩；
传统行业可以考虑使用外键：因为软件应用的人数是有限且可控的，数据库服务器的数据量也一般不会超大，且活跃数据有限。该场景中使用外键可以降低开发成本，借助 RDBMS 自身的触发器可以实现实体表与关联表之间的数据一致性和更新。另外，使用外键还可以做到开发人员和数据库设计人员（DBA）的分工，DBA 可以为程序员承担更多的工作量；

使用外键的守则

主表必须已经存在于数据库中，或者是当前正在创建的表。如果是后一种情况，则主表与从表是同一个表，这样的表称为自参照表，这种结构称为自参照完整性。
必须为主表定义主键。
外键中列的数目必须和主表的主键中列的数目相同。
外键中列的数据类型必须和主表主键中对应列的数据类型相同。

互联网应用应该尽量避免使用外键

不使用外键的原因其实很简单，因为 MySQL、PostgreSQL 等关系型数据库很难水平扩容，但是无状态的服务往往都可以很容易地扩容。由于外键等特性需要数据库执行额外的工作，而这些操作会占用数据库的计算资源，所以我们可以将大部分的需求都迁移到无状态的服务中完成以降低数据库的工作负载，从而避免数据库成为高并发性能的瓶颈。

另外，级联删除的出发点是为了保证数据的完整性，但是在设计关系表之间的不同关系时，我们也需要注意级联删除引起的数据大规模删除的问题。当客户端想要在数据库中删除 authos 表中的数据时，如果我们同时在 authors 和 posts 中指定了级联删除的行为，那么数据库会同时删除所有关联的 posts 记录以及与 posts 表关联的 comments 数据。

这种涉及多级的级联删除行为在数据量较小的数据库中不会导致问题，但是在数据量较大的数据库中删除关键数据可能会引起雪崩，一条记录的删除可能会被放大到几十倍甚至上百倍，这些对磁盘的随机 I/O 会带来巨大的开销，是我们想要尽可能避免的情况。如果我们能够较好地设计各个表之间的关系并且慎用 CASCADE 行为，这对于保证数据库中数据的合法性有着很重要的意义，使用该特性可以避免数据库中出现过期的、不合法的数据，但是在使用时也要合理预估可能造成的最坏情况。

在业务逻辑中模拟数据库外键

想要在应用程序中模拟数据库外键的功能其实比较容易，我们只需要遵循以下的几个准则：

向表中插入数据或者修改表中的数据时，都应该执行额外的 SELECT 语句确保它引用的数据在数据库中存在；
在删除数据之前需要执行额外的 SELECT 语句检查是否存在当前记录的引用；

需要注意的是为了保证一致性，我们需要在事务中执行上述的查询和修改语句，这样才能完整模拟外键的功能。例如：当我们向 posts 表中插入或者修改数据时，需要的处理相对比较简单，我们只需要执行有限的 SELECT 语句并按照如下所示的模式执行对应的操作就可以了：

BEGIN
SELECT * FROM authors WHERE id = <post.author_id> FOR UPDATE;
-- INSERT INTO posts ... / UPDATE posts ...
END

但是如果我们要删除 authors 表中的数据，就需要查询所有引用 authors 数据的表；如果有 10 个表都有指向 authors 表的外键，我们就需要在 10 个表中查询是否存在对应的记录，这个过程相对比较麻烦，不过也是为了实现完整性的必要代价，不过这种模拟外键方法其实远比使用外键更消耗资源，它不仅需要查询关联数据，还要通过网络发送更多的数据包。

手动实现数据库的级联删除操作也是可行的，如果我们在一个事务中按照顺序删除所有的数据，确实可以保证数据的一致性，但是这与外键的级联删除功能没有太大的区别，反而会有更差的表现。如果我们能够接受在一个时间窗口内的数据不一致，就可以将一个大号的删除任务拆成多个子任务分批执行，降低对数据库影响的峰值。

DELETE FROM posts WHERE author_id = 1 LIMIT 100;
DELETE FROM posts WHERE author_id = 1 LIMIT 100;
...
DELETE FROM authors WHERE id = 1;

注意，与数据库外键的 CASCADE 相比，这种方式会带来更大的额外开销，只是我们能降低对数据库性能的瞬时影响。

on delete/update 的外键关联操作类型

下文以 MySQL 为例。

no action（不采取行动）
cascade（级联）
set null（设为空）
restrict（禁止）

示例：

# 主表
create table country
(
	id int not null,
	name varchar(30),
	primary key(id)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

CASCADE（级联）约束方式

CASCADE（级联），表示主表在进行更新和删除时，更新和删除从表相对应的记录。

# 从表
create table solider
(
	id int not null,
	name varchar(30),
	country_id int,
	primary key(id),
	foreign key(country_id) references country(id) on delete cascade on update cascade
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

参照完整性测试：

# 插入主表记录
insert into country values(1, '西欧');
insert into country values(2, '玛雅');
insert into country values(3, '西西里');

# 插入从表记录
insert into solider values(1, '西欧见习步兵', 1);      # 插入成功
insert into solider values(2, '玛雅短矛兵', 2);       # 插入成功
insert into solider values(3, '西西里诺曼骑士', 3);   # 插入成功
insert into solider values(4, '法兰西剑士', 4);      # 插入失败，因为主表中不存在 id 为 4 的记录

约束方式测试：

insert into solider values(4, '玛雅猛虎勇士', 2); # 成功插入
delete from country where id=2;                 # 会导致从表中 id 为 2 和 4 的记录同时被删除
update country set id=8 where id=1;             # 会导致solider表中country_id为1的所有记录同时也会被修改为8

SET NULL（设空）约束方式

SET NULL（设空），表示主表进行更新和删除的时候，从表的对应字段被设为 NULL。

create table solider
(
	id int not null,
	name varchar(30),
	country_id int,
	primary key(id),
	foreign key(country_id) references country(id) on delete set null on update set null
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

参照完整性测试：

# 插入主表记录
insert into country values(1, '西欧');
insert into country values(2, '玛雅');
insert into country values(3, '西西里');

# 插入从表记录
insert into solider values(1, '西欧见习步兵', 1);    # 插入成功
insert into solider values(2, '玛雅短矛兵', 2);     # 插入成功
insert into solider values(3, '西西里诺曼骑士', 3); # 插入成功
insert into solider values(4, '法兰西剑士',4);     # 插入失败，因为主表中不存在 id 为 4 的记录

约束方式测试

insert into solider values(4, '西西里弓箭手', 3);  # 成功插入
delete from country where id=3;                 # 会导致从表中 id 为 3 和 4 的记录被设为 NULL
update country set id=8 where id=1;             # 导致从表中 country_id 为 1 的所有记录被设为 NULL

以 NO ACTION（不采取行动）或 RESTRICT（禁止）方式

NO ACTION（不采取行动）或 RESTRICT（禁止），限制在从表有关联记录的情况下，主表不能单独进行删除和更新操作。保持数据的强一致性。

注：在 MySQL 中，no action 与 restrict 的功能相同。而在其他数据库中，no action 可能为不进行任何操作。

create table solider
(
	id int not null,
	name varchar(30),
	country_id int,
	primary key(id),
	foreign key(country_id) references country(id) on delete RESTRICT on update RESTRICT
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

# or
create table solider
(
	id int not null,
	name varchar(30),
	country_id int,
	primary key(id),
	foreign key(country_id) references country(id) on delete no action on update no action
) ENGINE=InnoDB DEFAULT CHARSET=gb2312;

参照完整性测试：

# 插入主表记录
insert into country values(1, '西欧');
insert into country values(2, '玛雅');
insert into country values(3, '西西里');

# 插入从表记录
insert into solider values(1, '西欧见习步兵', 1);    # 插入成功
insert into solider values(2, '玛雅短矛兵', 2);     # 插入成功
insert into solider values(3, '西西里诺曼骑士', 3); # 插入成功
insert into solider values(4, '法兰西剑士',4);     # 插入失败，因为主表中不存在 id 为 4 的记录

约束方式测试

insert into solider values(4, '西欧骑士', 1);  # 成功插入
delete from country where id=1;               # 发生错误，从表中有关联记录，因此主表中不可删除相对应记录
update country set id=8 where id=1;           # 错误，从表中有相关记录，因此主表中无法修改。

本文地址：https://blog.csdn.net/Jmilk/article/details/108953563

关系型数据库的外键约束与关联

目录

文章目录

外键约束

外键关联

外键的作用

外键的性能问题

是否使用外键？

使用外键的守则

互联网应用应该尽量避免使用外键

在业务逻辑中模拟数据库外键

on delete/update 的外键关联操作类型

CASCADE（级联）约束方式

SET NULL（设空）约束方式

以 NO ACTION（不采取行动）或 RESTRICT（禁止）方式

如何区分SQL数据库中的主键与外键

MySQL外键约束的禁用与启用命令

如何区分SQL数据库中的主键与外键

Mysql数据库中数据表的优化、外键与三范式用法实例分析

MySQL外键约束的禁用与启用命令

关系型数据库的外键约束与关联

关系型数据库理论——范式的分类与判断

数据库的主表，从表，主键，外键等之间的关系

在Oracle数据库中添加外键约束的方法详解

Oracle数据库关于约束的创建与管理（外键约束）学习讲解