在关系型数据库中，数据通常分散在多个表里头，每个表负责存储整个系统某个特定方面的信息。这样的分工设计很棒，但同时也带来了一个小问题：数据关联起来可能会有点麻烦。
为了解决这个问题，关系型数据库依靠一些规则来强制表之间的关系。其中，外键（Foreign Key）就是维护这种结构和可靠性的一个非常重要的机制，它确保了相关数据始终保持一致。
在这篇文章里，我会详细聊聊外键是如何工作的。如果你刚刚开始接触数据库工程，我真的建议你先去看看一些关于关系型数据库入门和数据库设计的课程，这能帮你打下坚实的基础，更好地理解如何通过定义数据库模式来创建各种关系。
主键与外键：核心区别
主键和外键在关系型数据库中都扮演着维护结构和完整性的角色。让我来给你好好解释一下这俩。
什么是外键？
外键是一个表中的一个字段或一组字段，它指向另一个表中的主键。这个外键就像一条纽带，把两个表关联起来，确保引用表（子表）中的数据必须与被引用表（父表）中有效的条目匹配。
举个例子，假设你有一个数据库，里面有 users 表和 orders 表。orders 表可能包含一个 user_id 列，这个 user_id 必须与 users 表中已存在的 user_id 相匹配。外键约束就能确保你没法给一个不存在的用户创建订单。
理解外键，就离不开对主键的理解，因为外键的建立和维护关系都依赖于主键。
什么是主键？
主键是表中的一列或多列的组合，它能唯一标识表中的每一条记录。任何两行都不能共享相同的主键值，而且主键绝不能是 NULL。因此，一个表必须有一个主键，它是唯一标识符，被其他表的外键引用。
比方说，下面的查询创建了一个 users 表，其中 user_id 列就是主键：
-- 创建 users 表，user_id 作为主键
CREATE TABLE users (
user_id INT PRIMARY KEY,
username VARCHAR(100),
email VARCHAR(255)
);
主键和外键的主要差异
我把主键和外键之间的区别总结到了下面这个表里：
方面
主键 (Primary Key)
外键 (Foreign Key)
目的
唯一标识表中的每一条记录
通过引用另一个表的主键来创建关系
唯一性
必须是唯一的
可以包含重复值
NULL值
不能为 NULL
可以为 NULL (除非另有限制)
位置
在同一个表中定义
引用另一个表的主键
从上面的解释中，我们可以看出主键和外键对于创建结构化、可靠的数据库至关重要。它们协同工作，确保数据一致、关系得以维护，并最终保障了数据库的完整性。
在 SQL 中构建和管理外键
现在你已经明白了外键是什么以及它们在数据库中的重要性，接下来我们深入了解一下如何在设计数据库时定义、管理和控制它们的行为。
用 SQL 定义外键
在 SQL 中设置外键通常有两种办法：一种是在创建表的时候就定义好，另一种是如果表已经存在了，你可以在之后再添加。
下面的例子展示了在 SQL Server 中创建表时如何定义外键：
-- 创建父表: users
CREATE TABLE users (
user_id INT IDENTITY(1,1) PRIMARY KEY,
username NVARCHAR(100),
email NVARCHAR(255)
);
-- 创建子表: orders
CREATE TABLE orders (
order_id INT IDENTITY(1,1) PRIMARY KEY,
user_id INT,
order_date DATE,
CONSTRAINT fk_orders_users FOREIGN KEY (user_id)
REFERENCES users(user_id)
);
对于 PostgreSQL 来说，语法基本一样，不过我们在主键的自增方面通常用 SERIAL：
-- 创建父表: users
CREATE TABLE users (
user_id SERIAL PRIMARY KEY, -- SERIAL 用于自增
username VARCHAR(100),
email VARCHAR(255)
);
-- 创建子表: orders
CREATE TABLE orders (
order_id SERIAL PRIMARY KEY,
user_id INT,
order_date DATE,
CONSTRAINT fk_orders_users FOREIGN KEY (user_id)
REFERENCES users(user_id)
);
在 MySQL 中设置外键，你必须使用 InnoDB 存储引擎，就像下面这样：
-- 创建父表: users
CREATE TABLE users (
user_id INT AUTO_INCREMENT PRIMARY KEY,
username VARCHAR(100),
email VARCHAR(255)
) ENGINE=InnoDB; -- 必须使用 InnoDB 来支持外键
-- 创建子表: orders
CREATE TABLE orders (
order_id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT,
order_date DATE,
CONSTRAINT fk_orders_users FOREIGN KEY (user_id)
REFERENCES users(user_id)
) ENGINE=InnoDB;
如果你用的是 Oracle 数据库，同样可以定义外键，但要确保两个表的数据类型必须完全匹配。
-- 创建父表: users
CREATE TABLE users (
user_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
username VARCHAR2(100),
email VARCHAR2(255)
);
-- 创建子表: orders
CREATE TABLE orders (
order_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
user_id NUMBER,
order_date DATE,
CONSTRAINT fk_orders_users FOREIGN KEY (user_id)
REFERENCES users(user_id)
);
要为一个已经存在的表添加外键，可以使用下面的查询语句：
-- 假设 orders 表已经存在
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
REFERENCES users(user_id);
我个人觉得，如果你想深入了解 SQL 不同方言在创建数据库时的差异，可以多看看 SQL Server 和 PostgreSQL 数据库相关的课程。
管理约束和命名规范
外键其实也是一种约束，是数据库管理系统（DBMS）在执行插入、更新和删除等操作时，为验证数据而强制执行的规则。当一个外键约束激活时，数据库会确保外键列中输入的任何值，都必须已经在被引用的主键列中存在。
通常，我们会按照 fk_<子表名>_<父表名> 的模式来命名约束。比如在我们的例子中，外键名就成了 fk_orders_users。这样命名外键，不仅能让数据库模式更易读，也能帮助我们更快地调试约束违规问题，而且还很好地配合各种迁移工具。在执行下面这些操作时，约束验证都会生效：
INSERT: 如果引用的父值不存在，则拒绝插入该行。
UPDATE: 阻止会破坏关系的修改操作。
DELETE: 当存在依赖的子行时，阻止删除操作。
参照完整性与级联操作
参照完整性是外键约束所强制执行的核心原则。外键通过阻止“孤立记录”来帮助实现这一点，所谓的“孤立记录”，就是子表中的行指向了父表中不存在的行。
SQL 还允许你设置级联操作（cascading actions）。这些都是自动化指令，它告诉数据库管理系统（DBMS）如何处理对被引用记录的更改。例如，你可以使用这些级联操作来完成以下事情：
操作
行为
ON DELETE CASCADE
删除父行时，也删除子行
ON UPDATE CASCADE
父键更改时，自动更新子键
SET NULL
子外键变为 NULL
SET DEFAULT
子外键接收默认值
NO ACTION / RESTRICT
阻止会破坏关系更改的操作
举个例子，下面的查询就添加了级联操作，当用户删除订单时，相关用户记录也会被删除；如果 user_id 发生变化，则自动更新。
-- 添加级联规则，以强制执行层级清理
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
REFERENCES users(user_id)
ON DELETE CASCADE -- 删除用户时，也删除其订单
ON UPDATE CASCADE; -- 如果 user_id 更改，自动更新
设计表之间的关系
外键是数据建模中用来定义数据库表之间关系的重要构建块。在这一节里，我们会看看外键如何通过不同方法确保关联表之间的数据一致性。
一对多和多对多关系
在一个一对多（1:N）关系中，父表中的一行记录可以与子表中的多条记录关联。这种关系是通过将父表的主键（比如 users.user_id）作为外键放置在子表（比如 orders.customer_id）中来实现的。
例如，一个用户可以有多个订单，或者一个部门可以有多个员工。
一对多关系示例。图片来自作者。
另一方面，多对多（M:N）关系发生在当一个表中的多条记录可以与另一个表中的多条记录相关联时。由于你无法在规范化数据库中直接实现多对多关系，我们通常会使用一个中间表（或桥接表），它包含两个外键来建立这种关系。
多对多关系示例。图片来自作者。
复合外键和自引用外键
复合键（Composite Key）由两个或更多列组成，它们共同唯一标识一条记录，并且必须一起被引用。当自然键跨越多个字段，或者你想防止重复关系时，就会使用这种模式。例如，你可能有一个通过 (course_id, semester) 标识的课程，或者一个以 student_id 和 course_id 作为复合主键的中间表。
-- 定义 courses 表
CREATE TABLE course_offerings (
course_id INT,
semester VARCHAR(10),
-- 复合主键确保一门课程在一个学期内只能开设一次
PRIMARY KEY (course_id, semester)
);
-- 创建 enrollment 表
CREATE TABLE enrollment (
student_id INT,
course_id INT,
semester VARCHAR(10),
-- 此外键将选课记录链接到有效的课程
FOREIGN KEY (course_id, semester)
REFERENCES course_offerings(course_id, semester)
);
自引用外键（Self-referencing Foreign Key）或递归外键是指引用同一张表内主键的外键。这种模式在像“员工→经理”这样的层级数据中很常见。它允许在不创建额外表的情况下表示递归关系。
在下面的查询中，manager_id 引用了同一张表中的 employee_id。
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
name VARCHAR(100),
-- manager_id 引用了同一张表中的 employee_id
manager_id INT,
FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);
数据操作与完整性保障
现在我们知道外键是数据库中确保数据完整性的关键。在这一节中，我将探讨在进行 SQL 操作时，外键的一些实际考虑、常见挑战以及如何管理这些约束。
INSERT、UPDATE 和 DELETE 操作
我们来看看这些操作与外键是如何交互的。
带有外键的插入操作
要插入一条子记录，其引用的父值必须存在。如果不存在，插入操作就会失败。举个例子，下面的查询只有当 users 表中存在 user_id 为 1 的记录时，才会在 orders 表中插入记录。
-- users 表中包含 user_id = 1
INSERT INTO orders (order_id, user_id, order_date)
VALUES (100, 1, '2024-02-01');
如果你尝试为一个在父表中不存在的用户插入记录，你就会收到一个外键约束违规的错误。
-- user_id = 999 在 users 表中不存在
INSERT INTO orders (order_id, user_id, order_date)
VALUES (101, 999, '2024-02-01');
ERROR: insert or update on table "orders" violates foreign key constraint "fk_orders_users"
更新操作与级联行为
更新可以影响外键或主键表。例如，如果你更新 orders 表中的外键，只有当父表中存在主键时，更新才会成功。比如，下面的更新只有在 users.user_id = 5 存在时才会成功。
-- 更新外键值
UPDATE orders
SET user_id = 5
WHERE order_id = 100;
从上面的例子看，如果没有 ON UPDATE CASCADE，你就没法更新主键，因为子行仍然引用着 1。为了避免这种情况，我们先删除已有的外键约束，然后再重新创建，并启用 ON UPDATE CASCADE。用这个方法，相关的 orders.user_id 值就会自动更新。
-- 删除已有的外键约束
ALTER TABLE orders
DROP CONSTRAINT fk_orders_users;
-- 重新创建 FK 并启用 ON UPDATE CASCADE
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
REFERENCES users(user_id)
ON UPDATE CASCADE; -- 当父键更改时，自动更新子行
UPDATE users
SET user_id = 500
WHERE user_id = 1;
删除操作与级联
如果你尝试删除父行，但没有设置任何级联规则，删除操作会失败，并你会收到一个错误。当你添加 ON DELETE CASCADE 子句时，它会告诉数据库在父记录被删除时自动删除任何相关的子行，以保持数据一致性。
在下面的例子中，我们在 orders.user_id 上创建了一个外键，它引用了 users.user_id。有了 ON DELETE CASCADE，如果一个用户从 users 表中被删除了，所有相关的订单都会自动删除。
-- 向 orders 表添加一个新的外键约束
ALTER TABLE orders
ADD CONSTRAINT fk_orders_users
FOREIGN KEY (user_id)
REFERENCES users(user_id)
ON DELETE CASCADE; -- 当用户被删除时，自动删除订单
有时候，你可能希望数据库自动处理删除操作。你可以在创建表时使用 ON DELETE SET NULL 来强制实现这一点。这样一来，如果父记录发生更改或被删除，子表中的外键列就会被设置为 NULL。
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT, -- FK 引用 users.user_id
order_date DATE,
FOREIGN KEY (user_id)
REFERENCES users(user_id)
ON DELETE SET NULL -- 如果用户被删除，orders 中的 user_id 设置为 NULL
);
处理常见挑战
在设计复杂的数据库时，你很可能会遇到外键方面的一些挑战。下面是我个人经历过的一些常见外键问题，以及对应的实用解决方案。
数据类型不匹配：如果你的外键和被引用的主键列数据类型不兼容，就无法创建约束。为了避免这个，我建议相关表之间的数据类型始终保持一致。
循环依赖：当两个表相互引用时，就可能发生循环依赖，这会潜在地导致插入死锁。解决这个问题，你应该重新评估模式设计，或者先创建不带外键的表，加载基础数据，然后再用 ALTER TABLE 添加约束。
悬空外键：这个问题发生在子表的外键指向了一个不存在的父表记录。要解决它，可以使用级联操作在删除和更新操作期间验证数据。
管理修改和删除约束
有时候，在重新设计数据库模式时，你可能需要修改外键约束。以下是删除外键的方法，不同 SQL 方言之间语法略有差异。
在 PostgreSQL、SQL Server 和 Oracle 数据库中，我们使用 DROP CONSTRAINT 语句。
-- 删除外键
ALTER TABLE orders
DROP CONSTRAINT fk_orders_users;
然而，在 MySQL 中，我们使用 DROP FOREIGN KEY 语句来移除外键。
-- 删除外键
ALTER TABLE orders
DROP FOREIGN KEY fk_orders_users;
你需要注意的是，移除外键约束就等于移除了数据库对参照完整性的强制执行。删除之后，你就可以插入或更新违反关系的数据，这可能导致数据不一致或不正确。
性能和优化
从上面的例子中，我们已经看到外键如何通过维护数据完整性来确保有效的数据设计。不过，你也应该明白如何优化外键来提升数据库的性能。下面是我在使用外键时发现的一些很有用的技巧：
索引与查询性能
当你为外键创建索引后，数据库在进行插入、更新或删除操作时，就能通过快速定位父表中的对应键来验证更改。同样地，索引还能加速连接（join）操作，因为它允许数据库引擎根据外键值匹配行，从而提升了子表和父表之间的关联效率。
我个人实践下来的一个最佳做法就是，如果 DBMS 没有自动处理的话，你最好明确地为外键列创建索引。
查询优化技巧
有些 SQL 引擎会利用外键的存在来应用连接消除（join elimination），这是一种从查询执行计划中识别并移除不必要的连接的技术。举个例子，如果一个查询只访问子表中的列，但为了过滤目的包含了与父表的连接（而这个过滤条件又被外键约束所保证），那么这个连接操作就可以被优化掉。
你总是可以通过查看执行计划来了解查询优化器是如何处理外键和连接的。这些优化及其效率在不同的 SQL 引擎，比如 PostgreSQL、MySQL 和 SQL Server 之间，差异还是蛮大的。理解你特定数据库的优化器行为，有助于编写出能充分利用外键约束的高性能查询。
例如，你可以使用以下查询在 PostgreSQL 中查看执行计划：
-- 查看执行计划
EXPLAIN ANALYZE
SELECT o.order_id, u.email
FROM orders o
JOIN users u ON o.user_id = u.user_id;
下面这个表总结了如何在不同数据库中查看执行计划：
数据库
命令
PostgreSQL
EXPLAIN / EXPLAIN ANALYZE
MySQL
EXPLAIN
SQL Server
Estimated / Actual Execution Plan
Oracle
EXPLAIN PLAN FOR
我强烈建议大家去学习一下 SQL 中的数据连接课程，它会教你 SQL 中各种不同类型的连接，以及如何处理数据库中不同的关联表。
高级用例和最佳实践
除了基本的实现，你还可以利用外键来构建健壮、可扩展和易于维护的数据库架构。在这一节中，我将解释在使用外键时需要考虑的特殊场景和高级设计原则。
跨数据库或跨模式关系
尽管外键主要设计用于在单个数据库或模式内强制数据完整性，但更复杂的企业环境常常要求跨越这些边界建立关系。下面的表格总结了不同 SQL 方言对跨模式和跨数据库的支持情况。
数据库
跨模式支持
跨数据库支持
限制
PostgreSQL
是
否
仅限于单个数据库集群。
SQL Server
是
是
跨数据库完整性通常由应用程序逻辑或触发器管理，而非原生外键。
Oracle
是
是
数据库链接用于数据访问；原生外键约束通常不跨链接强制执行。
MySQL
否
否
严格限于同一数据库和存储引擎（如 InnoDB）内的表。
SQLite
否
否
仅限于特定的文件/数据库连接。
维护与审计
在构建外键时，你会发现它们会随着业务逻辑的变化而不断调整。请考虑以下方法来审计和维护外键，以确保它们始终保持最新：
审查约束：定期审计外键约束，确保它们与不断演进的业务规则和模式更改保持一致。
定期索引审计：始终验证所有外键列上都存在索引，以维护最佳的连接和验证性能，尤其是在大量数据导入或系统升级之后。
文档和可视化：使用实体关系（ER）图或模式可视化工具来记录关系。这些可视化辅助工具将确保所有开发者和分析师都能理解外键所强制执行的关系和完整性规则。
健壮数据库设计的最佳实践
为了确保你能够充分发挥外键的作用，请遵循以下指导原则：
让键与实际业务逻辑对齐：确保主键和外键真正代表了真实的实体以及它们之间的关系，而不是仅仅依赖于那些人工或不稳定的标识符。
使用清晰、一致的命名：选择描述性的约束名称，比如 fk_orders_user_id，这样你的模式会更容易维护、调试和与其他人协作。
平衡严格性与灵活性：约束应该保护数据质量，但你的设计也应该考虑到实际的边缘情况或由应用程序层处理的验证。
避免循环关系：构建你的模式，让外键依赖不要形成循环，因为这些循环会让插入、更新和删除变得不必要的复杂。
优化性能：正确地为外键建立索引，并谨慎应用级联操作，这样既能维护数据完整性，又不会拖慢你的系统。
结语
就像任何技能一样，熟练掌握外键也需要通过实践。我鼓励你多尝试模式建模、测试级联操作，并理解在实际场景中约束的行为。久而久之，你会学会如何在严格的规则与应用程序所需的灵活性之间找到平衡，从而创建出既有韧性又具适应性的数据库。
既然你已经了解了如何在数据库设计中使用外键，我建议你去尝试一些数据工程相关的职业路径，学习数据工程和数据仓库的基础知识。最后，如果你想提升你的大数据数据库管理技能，我推荐你去看看 Snowflake 中的数据建模入门课程，了解更多关于维度建模的知识。
关于
关注我获取更多资讯
📢 公众号
💬 个人号
本文链接地址：https://blog.eimoon.com/p/sql-foreign-key-database-relationships/
作者：eimoon.com
分享转载说明：本文由作者原创，转载请注明出处。