MySQL / 系统课时
一对一、一对多与多对多
一对一、一对多与多对多
表关系描述的是不同实体之间如何关联。理解一对一、一对多和多对多后,就能判断外键应该放在哪里、什么时候需要中间表,并为后续多表查询建立清晰的数据模型。
学习目标
- 区分一对一、一对多和多对多三类常见关系
- 根据业务关系确定外键和中间表的位置
- 使用 MySQL 表结构表达三类关系
从业务关系判断基数
关系类型关注的是一条记录最多能对应另一张表中的多少条记录。例如一个用户只有一份用户档案,可以建模为一对一;一个部门拥有多个员工,而一个员工只属于一个部门,是典型的一对多;一名学生可以选择多门课程,一门课程也可以被多名学生选择,则属于多对多。

一对一使用唯一外键
一对一通常把外键放在依赖性更强的一方,并使用 UNIQUE 限制同一个主记录只能出现一次。下面用用户与用户档案演示这种约束。
sql
CREATE TABLE users (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) NOT NULL
);
CREATE TABLE user_profiles (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
user_id BIGINT NOT NULL UNIQUE,
nickname VARCHAR(50),
CONSTRAINT fk_profile_user
FOREIGN KEY (user_id) REFERENCES users(id)
);预期结果:两张表创建成功,并且 user_profiles.user_id 同时具有外键和唯一性约束,因此同一个用户不能拥有两条档案记录。
text
Query OK实际客户端显示的执行耗时等信息可能不同。
一对多把外键放在多的一侧
一对多是业务系统中最常见的关系。部门无需保存员工 ID 列表,而是在员工表保存 department_id。这样每个员工只引用一个部门,同一个部门 ID 却可以被多条员工记录引用。
sql
CREATE TABLE departments (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL
);
CREATE TABLE employees (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL,
department_id BIGINT NOT NULL,
CONSTRAINT fk_employee_department
FOREIGN KEY (department_id) REFERENCES departments(id)
);预期结果:多个 employees 记录可以保存相同的 department_id,形成一个部门对应多个员工的关系。
text
Query OK多对多需要中间表
关系两侧都可能出现多条对应记录时,不能简单地在任意一张主表放一个外键,因为单个字段无法自然保存多个关联 ID。常见做法是新增中间表,每一行只表示一组关联。
sql
CREATE TABLE students (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL
);
CREATE TABLE courses (
id BIGINT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100) NOT NULL
);
CREATE TABLE student_courses (
student_id BIGINT NOT NULL,
course_id BIGINT NOT NULL,
PRIMARY KEY (student_id, course_id),
FOREIGN KEY (student_id) REFERENCES students(id),
FOREIGN KEY (course_id) REFERENCES courses(id)
);预期结果:同一学生可以在 student_courses 中关联多门课程,同一课程也可以关联多名学生;联合主键还能避免同一选课关系被重复插入。
text
Query OK建模时可以先问两个问题:左侧一条记录最多对应右侧多少条,右侧一条记录最多又对应左侧多少条。答案是 1 和 1 时通常是一对一,1 和 N 是一对多,N 和 N 则应优先考虑中间表。
按自己的节奏,确认真正理解。