除非另外命名了方言,否则 SQL 示例使用 PostgreSQL。简短的代码片段假定其引用的表已存在。在应用 DDL 之前,请在目标数据库中验证 DDL。
理解 SQL 关系模式
关系型数据库的力量源于表之间的连接。无论您是在设计小型应用程序还是企业级数据仓库,选择正确的关联模式都决定了您存储、查询和维护数据的效率。这三个基本模式——一对一, 一对多, 以及 多对多— 涵盖许多常见关联;递归和高阶关系需要额外的建模。
在本指南中,我们通过 PostgreSQL 示例剖析每个模式,探讨连接表的作用,并展示这些关系如何在实体关系图(ERD)上呈现。如果你想现在就可视化你自己的架构,请打开 Erd Go 编辑器 和粘贴支持 CREATE TABLE 定义以生成图表。
一对多关系
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 编辑器— 只需几秒钟,就能节省数小时的调试时间。



