返回所有指南
数据库设计··阅读约需 6 分钟

一对多与多对多:SQL 关系模式详解

学习 SQL 中的一对多与多对多关系。了解如何使用外键、连接表和真实世界示例来实现它们。

客户到订单的外键和学生到课程的连接表,带有复合主键
在本指南中
  1. 理解 SQL 关系模式
  2. 一对多关系
  3. 多对多关系
  4. 一对一关系
  5. 关系在 ERD 中如何显示
  6. 选择正确的关联模式
  7. 参考资料与延伸阅读

除非另外命名了方言,否则 SQL 示例使用 PostgreSQL。简短的代码片段假定其引用的表已存在。在应用 DDL 之前,请在目标数据库中验证 DDL。

理解 SQL 关系模式

关系型数据库的力量源于表之间的连接。无论您是在设计小型应用程序还是企业级数据仓库,选择正确的关联模式都决定了您存储、查询和维护数据的效率。这三个基本模式——一对一, 一对多, 以及 多对多— 涵盖许多常见关联;递归和高阶关系需要额外的建模。

在本指南中,我们通过 PostgreSQL 示例剖析每个模式,探讨连接表的作用,并展示这些关系如何在实体关系图(ERD)上呈现。如果你想现在就可视化你自己的架构,请打开 Erd Go 编辑器 和粘贴支持 CREATE TABLE 定义以生成图表。

外键 SQL 已映射到客户和订单之间的 ERD 关系
外键不仅仅是语法:在 ERD 中,它变成了关系线,解释了表之间如何相互依赖。

一对多关系

A 一对多 (1:N) 关系是数据库设计中最常见的模式。这意味着单行在 父 表可以被许多行引用 子项 表,并且非空子外键指向一个父项。可为空的外键允许没有父项的子项。

经典示例:部门与员工

一家公司有许多部门,每个部门包含多名员工。但是,在任何给定时间,每名员工都只属于一个部门。

CREATE TABLE departments (
    id          SERIAL PRIMARY KEY,
    name        VARCHAR(100) NOT NULL,
    location    VARCHAR(255),
    created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE employees (
    id              SERIAL PRIMARY KEY,
    first_name      VARCHAR(50) NOT NULL,
    last_name       VARCHAR(50) NOT NULL,
    email           VARCHAR(255) UNIQUE NOT NULL,
    department_id   INT NOT NULL,
    hire_date       DATE DEFAULT CURRENT_DATE,
    CONSTRAINT fk_department
        FOREIGN KEY (department_id)
        REFERENCES departments (id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE
);

关键实现细节

  • 外键(department_id) 总是位于 多 端—— employees 表在此情况下。
  • ON DELETE RESTRICT 防止意外删除仍分配有员工的部门。
  • ON UPDATE CASCADE 确保如果部门的主键发生更改,所有员工行都会自动更新。
  • 添加索引于 department_id 有助于连接和部门过滤;请通过具有代表性的数据和执行计划来确认其益处。

查询一对多数据

-- All employees in the Engineering department
SELECT e.first_name, e.last_name, d.name AS department
FROM employees e
JOIN departments d ON d.id = e.department_id
WHERE d.name = 'Engineering';

-- Count employees per department
SELECT d.name, COUNT(e.id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.department_id = d.id
GROUP BY d.id, d.name
ORDER BY headcount DESC;

其他常见的“一对多”示例包括 作者 → 博客文章, customers → orders, 以及 类别 → 产品.

多对多关系

A 多对多 (M:N) 关系存在于 两者 两侧可以与另一侧的多行关联。关系数据库无法仅用单个外键直接表示这一点,因此我们引入了一个 连接表 (也称为 桥接, 连接, 或 关联的 表)。

经典示例:学生与课程

一个学生可以注册多门课程,每门课程可以有多个学生。连接表 enrollments 位于它们之间并保存指向两侧的外键。

CREATE TABLE students (
    id          SERIAL PRIMARY KEY,
    full_name   VARCHAR(120) NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL,
    enrolled_on DATE DEFAULT CURRENT_DATE
);

CREATE TABLE courses (
    id          SERIAL PRIMARY KEY,
    title       VARCHAR(200) NOT NULL,
    credits     SMALLINT NOT NULL DEFAULT 3,
    department  VARCHAR(100)
);

CREATE TABLE enrollments (
    student_id  INT NOT NULL REFERENCES students (id) ON DELETE CASCADE,
    course_id   INT NOT NULL REFERENCES courses (id) ON DELETE CASCADE,
    enrolled_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    grade       CHAR(2),
    PRIMARY KEY (student_id, course_id)
);

为什么需要连接表?

  • 这 复合主键 (student_id, course_id) 允许每个“学生-课程”对有一行。如果需要跨学期重复注册,请在业务键中包含学期或分别对每次注册进行建模。
  • 你可以将额外的属性附加到关系本身,例如 grade 或 enrolled_at, 它们不单独属于任何实体。
  • ON DELETE CASCADE 在外键上意味着删除学生或课程会自动清理注册行。

查询多对多数据

-- All courses for a specific student
SELECT c.title, c.credits, e.grade
FROM enrollments e
JOIN courses c ON c.id = e.course_id
WHERE e.student_id = 42;

-- Students enrolled in 'Database Systems'
SELECT s.full_name, s.email
FROM enrollments e
JOIN students s ON s.id = e.student_id
JOIN courses c ON c.id = e.course_id
WHERE c.title = 'Database Systems';

实际的多对多示例包括 用户 ↔ 角色, products ↔ tags, 演员 ↔ 电影, 以及 orders ↔ products (通过 order_items 同时存储数量和价格的连接表)。

一对一关系

A 一对一 (1:1) 关系意味着表 A 中的每一行最多映射到表 B 中的一行,反之亦然。这种模式较少见,但对于将极少访问的列或敏感列拆分到单独的表中非常有用。

CREATE TABLE users (
    id          SERIAL PRIMARY KEY,
    username    VARCHAR(60) UNIQUE NOT NULL,
    email       VARCHAR(255) UNIQUE NOT NULL
);

CREATE TABLE user_profiles (
    user_id     INT PRIMARY KEY REFERENCES users (id) ON DELETE CASCADE,
    bio         TEXT,
    avatar_url  VARCHAR(500),
    birthdate   DATE,
    updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

注意 user_profiles.user_id 既是 主键 和 外键。此约束在数据库级别强制执行一对一映射——每个用户最多可以有一个配置文件行。

关系在 ERD 中如何显示

实体关系图(ERD)使用表之间的线条和符号来一目了然地传达基数。最流行的两种符号是 陈氏标记法(Crow’s Foot) 和 陈氏 符号。

关系陈氏标记法符号如何阅读
一对一||—||两侧各正好一个
一对多||—<左侧一个,右侧多个(乌鸦脚端)
多对多>—<双方均为多对多;通过连接表实现

这些文本符号是最大基数的简写。可选性需要额外的零或一标记;仅外键并不要求父项有任何子项。

当您在 ERD 工具中对多对多关系进行建模时,通常会看到三个框——两个实体表和它们之间的连接表,每个表都通过一对多关系线连接。

将本文中的任一 SQL 示例粘贴到 Erd Go 编辑器 来查看自动生成的图表,其中包含外键箭头和基数指示符。

选择正确的关联模式

选择错误的基数会导致数据重复、查询复杂和完整性错误。使用此决策框架:

  • A 侧的一行可以与 B 侧的多行相关联,反之则不行吗? → 一对多。将外键放在 B 端。
  • 双方的行都可以与另一方的多行相关联吗? → 多对多。创建一个带有复合键的连接表。
  • 映射是否严格为 1:1 且你需要进行表分离? → 一对一。使用与外键相同的主键。

如有疑问,请先绘制模式草图。快速的 ERD 可以比在生产环境中遇到约束错误更早地发现关系问题。尝试在以下位置构建您的图: Erd Go 编辑器— 只需几秒钟,就能节省数小时的调试时间。

参考资料与延伸阅读

更多文章