MySQL 表关系与外键

数据库通常将相关信息存储在不同的表中，以保持组织性和效率。例如，一个数据库可能有一个用于存储客户详细信息的表，以及另一个用于存储他们订单的表。为了连接这些分散的信息并重建数据的完整视图，我们需要在表之间建立关系 (Relationships)。这些关系主要通过使用外键 (Foreign Keys) 来管理。

1. 理解关系型数据库设计原则

关系型数据库是围绕“相关表”的概念构建的，其中每个表保存特定类型的信息。这种被称为规范化 (Normalization) 的方法有助于减少数据冗余并提高数据完整性。与其将所有客户和订单的详细信息存储在一个大表中（这会导致每次客户下订单时重复客户信息），不如将数据拆分成逻辑单元。

1.1 主键 (Primary Keys) 与唯一标识

在探索外键之前，回顾模块 2 中涉及的主键的作用非常重要。主键是唯一标识表中每一行的列（或一组列）。它确保每条记录都是独特的，并且可以无歧义地被引用。

例如，考虑一个 Customers（客户）表：

在这里，customer_id 是主键。每个 customer_id 都是唯一的，并标识一个特定的客户。

1.2 表关系的必要性

想象一个客户可以下多个订单的场景。如果我们试图将所有订单信息直接存储在 Customers 表中，我们要么必须为同一个客户创建多行（违反了 customer_id 主键的唯一性），要么以一种查询起来非常麻烦的方式存储订单详细信息（例如，在单个列中作为逗号分隔的列表）。这两种方法都不高效，也不符合良好的数据库设计原则。

相反，我们会创建一个单独的 Orders（订单）表来存储特定于订单的详细信息：

这个 Orders 表拥有自己的主键 order_id。然而，现在没有直接的方法可以知道哪个客户下了哪个订单。这就是外键变得必不可少的地方。

2. 外键 (Foreign Keys)：连接表与表

外键是一个表中的一列（或一组列），它引用另一个表中的主键。包含外键的表被称为引用表 (referencing table)（或子表），包含它所引用的主键的表被称为被引用表 (referenced table)（或父表）。

外键的目的是在两个表之间建立连接，强制执行参照完整性 (Referential Integrity)。参照完整性确保表之间的关系保持一致。例如，你不能为一个不存在的客户创建订单；如果在系统中仍有与该客户关联的订单，你也无法删除该客户（除非为这些情况定义了特定的动作规则）。

2.1 外键如何工作

让我们将外键概念整合到我们的 Customers 和 Orders 表中。为了将订单链接到客户，我们在 Orders 表中添加一列，该列将保存来自 Customers 表的 customer_id。Orders 表中的这个新列就是我们的外键。

修改后的 Orders 表：

在这个修改后的 Orders 表中，customer_id 列是一个外键，它引用了 Customers 表中的 customer_id（主键）。

• 订单 101 是由客户 1（Alice Smith）下的。
• 订单 102 是由客户 2（Bob Johnson）下的。
• 订单 103 也是由客户 1（Alice Smith）下的，这表明一个主键可以被多个外键值引用。

2.2 定义外键

在 MySQL 中创建或修改表时，你需要明确定义外键约束。这涉及到指定子表（引用表）中的列以及父表（被引用表）中的主键列。

子表中的外键列必须具有与其在父表中引用的主键列兼容的数据类型。例如，如果 Customers 表中的 customer_id 是 INT，那么 Orders 表中的 customer_id 也必须是 INT。

3. 表关系的类型

外键促成了表之间不同类型的关系：

3.1 一对多关系 (One-to-Many Relationship)

这是最常见的关系类型。父表中的一行可以与子表中的多行相关联。

• 示例： 一个客户可以下许多订单。（Customers (父) 到 Orders (子)）。外键 (customer_id) 位于 Orders 表中。

3.2 一对一关系 (One-to-One Relationship)

（较少见，但也存在）
父表中的一行恰好与子表中的一行相关联。这通常用于为了性能或安全原因而拆分一个非常宽（列很多）的表，或者用于存储可选属性。

• 示例： 一个 Users（用户）表和一个 User_Profiles（用户资料）表。每个用户只有一个资料。外键可以在任何一个表中，引用另一个表的主键，通常在外键列上带有一个 UNIQUE 约束来强制执行一对一的规则。或者，子表的主键也可以兼作外键。

3.3 多对多关系 (Many-to-Many Relationship)

第一个表中的一行可以与第二个表中的多行相关联，而第二个表中的一行也可以与第一个表中的多行相关联。

• 示例： 许多学生可以注册许多课程，许多课程可以有许多学生。

这种关系不能用单个外键直接实现。相反，它需要一个关联表 (associative table)（也称为连接表或桥接表），该表包含来自两个表的外键。
对于 Students（学生）和 Courses（课程），会创建一个 Enrollments（注册）表，其中包含 student_id（指向 Students 的外键）和 course_id（指向 Courses 的外键）。Enrollments 表的主键通常是 student_id 和 course_id 组成的复合键 (composite key)。

4. 参照完整性与外键约束

外键约束对于维护参照完整性至关重要。这意味着要确保表之间的关系保持有效。当你定义外键时，MySQL 会施加规则来防止那些会破坏这些链接的操作。

为外键定义的常见动作规则包括：

• ON DELETE CASCADE（级联删除）： 如果父表中的某一行被删除，子表中所有对应的行也会被自动删除。
• ON UPDATE CASCADE（级联更新）： 如果父表中的某一行的主键被更新，子表中相应的外键值也会被自动更新。
• ON DELETE SET NULL（删除设空）： 如果父表中的某一行被删除，子表中的外键列将被设置为 NULL。这要求外键列必须允许为空。
• ON UPDATE SET NULL（更新设空）： 如果父表中的某一行的主键被更新，子表中的外键列将被设置为 NULL。
• ON DELETE RESTRICT（限制删除，通常是默认行为）： 如果存在依赖的子行，则阻止删除父行（会报错）。
• ON UPDATE RESTRICT（限制更新，默认行为）： 如果存在依赖的子行，则阻止更新父主键。

5. 实际示例与演示

让我们使用一个基于图书管理系统的简化场景来演示外键的创建和行为。我们将创建两个表：Authors（作者）和 Books（书籍）。

示例 1：创建带有外键的表

首先，我们创建 Authors 表（父表）。

CREATE TABLE Authors (
    author_id INT PRIMARY KEY AUTO_INCREMENT,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    birth_year INT
);

接下来，我们创建 Books 表（子表）并定义外键。

CREATE TABLE Books (
    book_id INT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(100) NOT NULL,
    publication_year INT,
    author_id INT, -- 这将是我们的外键
    FOREIGN KEY (author_id) REFERENCES Authors(author_id)
    -- 我们将 Books 中的 'author_id' 链接到 Authors 中的 'author_id' (主键)
    ON DELETE RESTRICT -- 默认行为：如果存在书籍，则阻止删除作者
    ON UPDATE CASCADE -- 如果 author_id 发生变化（对于主键来说很少见），在 Books 表中同步更新
);

示例 2：插入数据并演示参照完整性

现在，尝试为一个不存在的作者插入一本书：

-- 尝试为 Authors 表中不存在的 author_id 插入一本书
INSERT INTO Books (title, publication_year, author_id) VALUES
('Nonexistent Book', 2020, 99);

这个 INSERT 语句将会失败，并显示类似 Cannot add or update a child row: a foreign key constraint fails 的错误，因为 author_id = 99 在 Authors 表中不存在。这演示了实际运作中的参照完整性，它阻止了“孤儿”记录（orphaned records）的产生。

示例 3：演示 ON DELETE RESTRICT

尝试删除一位仍然有相关书籍的作者：

-- 假设 Stephen King 的 author_id 为 1，且他的书已在 Books 表中
DELETE FROM Authors WHERE author_id = 1;

这个 DELETE 语句同样会失败并报错 Cannot delete or update a parent row: a foreign key constraint fails。因为在 Books 表中有引用 author_id = 1 的书籍，而我们指定了 ON DELETE RESTRICT。

要成功删除该作者，你需要首先：

1. 从 Books 表中删除他写的所有书。
2. 或者将这些书的 author_id 更改为另一个现有的作者（如果适用）。

如果我们在创建外键时定义了 ON DELETE CASCADE，那么删除作者将会自动从 Books 表中删除他所有的书。