用中间表建模MANY-TO-MANY关系

“多对多”关系就是说，一张表里的某条记录可以和另一张表的多条记录关联，反过来也一样。比如： - 一个学生（在students表里）可以选修多个课程（在courses表里）。 - 一个课程可以被多个学生选修。

问题是，这种关系直接存很麻烦。所以我们要用中间表来存这些关系。

现实生活中的例子

想象一下，你有一个学生表和一个课程表。如果你想在每个表里加一列来存所有相关的数据，场面会很混乱：

• 在students表里得存学生选的所有课程的列表。那怎么存？用逗号分隔？用数组？这样查询会很难搞。
• 在courses表里得存所有学生的列表，这更让人头大。

所以，正确的做法是建第三张表，专门存学生和课程之间的关系。

中间表：我们的救星！

中间表（有时候叫关联表）能解决所有问题。它有两个外键：

• 指向students表的外键。
• 指向courses表的外键。

中间表的每一条记录，就是某个学生和某个课程的关联。

为“多对多”关系建表

来点实操！下面是怎么为学生和课程之间的关系建表：

第1步：创建students表

这是我们的学生表。这里存学生的唯一ID和名字。

CREATE TABLE students (
    student_id SERIAL PRIMARY KEY,
    name TEXT NOT NULL
);

• student_id —— 学生的唯一ID（自增，多亏了SERIAL！）。
• name —— 学生的名字。

第2步：创建courses表

现在来建课程表。这里存课程的唯一ID和名字。

CREATE TABLE courses (
    course_id SERIAL PRIMARY KEY,
    title TEXT NOT NULL
);

• course_id —— 课程的唯一ID。
• title —— 课程名。

第3步：创建中间表enrollments

现在来建我们的神奇关联表。它有两列，每列都是外键，指向对应的表。

CREATE TABLE enrollments (
    student_id INT REFERENCES students(student_id),
    course_id INT REFERENCES courses(course_id),
    PRIMARY KEY (student_id, course_id)
);

结构解析：

• student_id —— 外键，指向students表的student_id。
• course_id —— 外键，指向courses表的course_id。
• PRIMARY KEY (student_id, course_id) —— 主键是这两个外键的组合，保证每个关系都是唯一的。

插入数据

来插点数据，看看效果。

第1步：添加学生

INSERT INTO students (name) VALUES
('Alice'),
('Bob'),
('Charlie');

结果：

第2步：添加课程

INSERT INTO courses (title) VALUES
('Mathematics'),
('History'),
('Biology');

结果：

第3步：往enrollments插入记录

现在让学生选课。比如：

• Alice选了Mathematics和History。
• Bob只选了Biology。
• Charlie选了所有课程。

INSERT INTO enrollments (student_id, course_id) VALUES
(1, 1), -- Alice 选 Mathematics
(1, 2), -- Alice 选 History
(2, 3), -- Bob 选 Biology
(3, 1), -- Charlie 选 Mathematics
(3, 2), -- Charlie 选 History
(3, 3); -- Charlie 选 Biology

结果：

“多对多”关系的查询

现在有了数据，是时候用起来了！

怎么查某个学生选了哪些课？

比如想知道Alice（ID = 1）选了哪些课，可以查：

SELECT c.title
FROM courses c
JOIN enrollments e ON c.course_id = e.course_id
WHERE e.student_id = 1;

结果：

怎么查某门课有哪些学生？

比如想知道谁选了Mathematics（ID = 1）：

SELECT s.name
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
WHERE e.course_id = 1;

结果：

怎么查所有学生和他们的课程？

想看全局，谁选了啥课，可以查：

SELECT s.name AS student, c.title AS course
FROM students s
JOIN enrollments e ON s.student_id = e.student_id
JOIN courses c ON e.course_id = c.course_id;

结果：

enrollments表让我们的结构很灵活——可以随时加删学生和课程的关系，主表不用动。用JOIN查数据也很方便，比如查谁选了什么课。外键还能自动帮我们保证数据不会乱，比如不会有人选了根本不存在的课程。

“多对多”关系常见错误

没有唯一约束：如果不设PRIMARY KEY，可能会不小心插入重复的关系。

数据完整性被破坏：插入不存在的student_id或course_id会报错。

删除顺序不对：如果先删了courses表里的课程，enrollments表里会留下“孤儿”记录。为避免这种情况，外键定义时加上ON DELETE CASCADE。