SQL – 多对多表的主键

在本文中，我们将介绍多对多表的主键。多对多关系是数据库设计中常见的一种关系类型，它将两个表通过一个中间表进行连接。在这种关系中，中间表的主键起着至关重要的作用。

阅读更多：SQL 教程

多对多关系简介

多对多关系是指两个实体集之间存在着复杂的关联。举个例子，一个学生可以选修多门课程，而一门课程也可以被多个学生所选修。这种关系无法通过简单的一对多或多对一关系来描述，因此需要借助中间表来处理。

中间表的设计

中间表用于连接两个实体集，它包含了这两个实体集的主键作为外键。在多对多关系中，中间表的主键扮演着连接实体集的桥梁角色。

下面以一个学生和课程之间的选课关系为例，来说明中间表的设计。假设我们有两个表：学生表（Students）和课程表（Courses），学生表包含了学生的学号（StudentID）和姓名（Name），课程表包含了课程号（CourseID）和名称（Name）。

为了建立学生和课程之间的多对多关系，我们需要创建一个中间表（StudentCourse），其中包含了学生表和课程表的主键。中间表的设计如下：

CREATE TABLE StudentCourse (
  StudentID INT,
  CourseID INT,
  PRIMARY KEY (StudentID, CourseID),
  FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
  FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);

在上述设计中，中间表的主键由学生表的主键和课程表的主键组成，这样就确保了每个学生和课程的组合都是唯一的。

多对多关系的查询

在多对多关系中，查询通常涉及到多个表的连接操作。我们可以使用JOIN语句来连接学生表、中间表和课程表，从而获取学生选修的所有课程。下面是一个示例查询：

SELECT Students.Name, Courses.Name
FROM Students
JOIN StudentCourse ON Students.StudentID = StudentCourse.StudentID
JOIN Courses ON StudentCourse.CourseID = Courses.CourseID;

上述查询将返回学生的姓名和选修课程的名称。

多对多关系的插入

在多对多关系中，插入操作涉及到向多个表中插入数据。首先，我们需要向学生表和课程表中插入对应的记录。然后，我们需要向中间表插入学生和课程的对应关系。

下面是一个示例插入操作：

INSERT INTO Students (StudentID, Name) VALUES (1, '张三');
INSERT INTO Courses (CourseID, Name) VALUES (1, '数学');

INSERT INTO StudentCourse (StudentID, CourseID) VALUES (1, 1);

上述操作将创建一个学生记录、一个课程记录，并将它们的对应关系插入到中间表中。

多对多关系的删除

在多对多关系中，删除操作也涉及到多个表的操作。首先，我们需要在中间表中删除学生和课程的对应关系。然后，根据需要，我们可以选择从学生表和课程表中删除对应的记录。

下面是一个示例删除操作：

DELETE FROM StudentCourse WHERE StudentID = 1 AND CourseID = 1;

-- 可选：删除学生记录和课程记录
DELETE FROM Students WHERE StudentID = 1;
DELETE FROM Courses WHERE CourseID = 1;

上述操作将从中间表中删除学生和课程的对应关系，并在需要时删除学生记录和课程记录。