SQL – 多对多表主键

在本文中，我们将介绍SQL中多对多表的主键的使用方法及示例说明。

阅读更多：SQL 教程

多对多关系

在关系型数据库中，多对多关系是指两个实体之间存在多对多的关联。例如，一个学生可以选择多个课程，一个课程也可以有多个学生选修。为了在数据库中表示这种关系，我们需要使用多对多关系表。

多对多关系表的设计

多对多关系表的设计通常需要额外的一张表来存储两个实体之间的关联关系。这张关系表一般包含至少两个外键列，分别指向两个实体的主键。

举一个例子，假设我们有两张表，分别是学生表（students）和课程表（courses），它们的结构如下：

学生表（students）：

+------+--------+-----------+
| id   | name   | age       |
+------+--------+-----------+
| 1    | Alice  | 18        |
| 2    | Bob    | 20        |
| 3    | Charlie| 19        |
+------+--------+-----------+

课程表（courses）：

+------+---------+---------+
| id   | name    | teacher |
+------+---------+---------+
| 1    | Math    | Tom     |
| 2    | English | Lily    |
| 3    | Physics | Jack    |
+------+---------+---------+

为了表示学生和课程之间的多对多关系，我们可以创建一张关系表（student_courses），将两个表的主键作为外键列，如下所示：

关系表（student_courses）：

+---------+-------------+
| student | course      |
+---------+-------------+
| 1       | 1           |
| 1       | 2           |
| 2       | 1           |
| 3       | 2           |
| 3       | 3           |
+---------+-------------+

在关系表中，每一行表示一个学生选修了一门课程。通过关系表，我们可以轻松地查询某个学生选修的所有课程，或者某门课程被哪些学生选修。

多对多关系表主键的选择

在多对多关系表中，我们需要选择一个合适的主键来唯一标识每一行数据。常见的主键选择有以下几种：

自增主键：在关系表中添加一个自增的整数列作为主键。这种方法简单、高效，且能保证唯一性。例如：

+----+---------+-------------+
| id | student | course      |
+----+---------+-------------+
| 1  | 1       | 1           |
| 2  | 1       | 2           |
| 3  | 2       | 1           |
| 4  | 3       | 2           |
| 5  | 3       | 3           |
+----+---------+-------------+

复合主键：使用多个列作为主键，保证它们的组合唯一。例如：

+---------+-------------+
| student | course      |
+---------+-------------+
| 1       | 1           |
| 1       | 2           |
| 2       | 1           |
| 3       | 2           |
| 3       | 3           |
+---------+-------------+

在这个例子中，组合(student, course)需要保证唯一，即一个学生不能重复选修同一门课程。

唯一标识符主键：如果关系表中已经存在一个唯一标识符，可以直接将其作为主键。例如：

+---------+-------------+
| student | course      |
+---------+-------------+
| Alice   | Math        |
| Alice   | English     |
| Bob     | Math        |
| Charlie | English     |
| Charlie | Physics     |
+---------+-------------+

在这个例子中，学生和课程的姓名组合(student, course)为主键，保证唯一性。

选择哪种主键取决于具体的应用场景和性能要求。自增主键是最常见的选择，因为它简单、高效且容易实现。

示例说明

假设我们使用自增主键来标识关系表中的行，我们可以使用以下SQL语句来创建多对多关系表及示例数据：

-- 创建关系表
CREATE TABLE student_courses (
  id INT AUTO_INCREMENT PRIMARY KEY,
  student_id INT,
  course_id INT,
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id)
);

-- 插入示例数据
INSERT INTO student_courses (student_id, course_id) VALUES (1, 1);
INSERT INTO student_courses (student_id, course_id) VALUES (1, 2);
INSERT INTO student_courses (student_id, course_id) VALUES (2, 1);
INSERT INTO student_courses (student_id, course_id) VALUES (3, 2);
INSERT INTO student_courses (student_id, course_id) VALUES (3, 3);