sql-composite-key

SQL 复合主键

什么是复合主键？

复合主键（Composite Key）是由两个或多个列组成的主键。这些列的组合对于表中的每一行都必须是唯一的，确保每条记录都能被唯一标识。复合主键中的单个列可以包含重复值，但它们的组合值不能重复。

本质上，它是一个多列主键。所有作为主键（简单主键或复合主键）一部分的列都必须定义为 NOT NULL。

为什么要使用复合主键？

复合主键最常用于“连接表”（linking table）或“交叉表”（junction table），它们用于建模两个其他表之间的多对多关系。

考虑一个包含 Users（用户）和 Products（产品）的电商数据库。一个用户可以注册多门课程，一门课程也可以有多个用户。为了建模这种关系，我们创建一个 Enrollments（注册）表。在此表中，单独的 UserID 或 CourseID 列不足以唯一标识一行，因为一个用户可以注册多门课程，一门课程也可以有多个用户。然而，(UserID, CourseID) 的组合是唯一的，因为一个用户只能注册同一门课程一次。这种组合是复合主键的完美候选。

创建带复合主键的表

语法

你可以在创建表时，通过对多列使用 PRIMARY KEY 约束来定义复合主键。

CREATE TABLE table_name (
    column1_name data_type NOT NULL,
    column2_name data_type NOT NULL,
    ...
    CONSTRAINT constraint_name PRIMARY KEY (column1_name, column2_name)
);

为你的约束命名（如下面的 pk_enrollments）是一种最佳实践。它使得将来引用或删除它变得更加容易。

示例

我们来创建 Enrollments 表。它连接了 Users 和 Courses 表（我们假设它们已经存在）。主键是 user_id 和 course_id 的复合主键。

CREATE TABLE Enrollments (
    user_id INT NOT NULL,
    course_id INT NOT NULL,
    enrollment_date DATE NOT NULL,
    grade CHAR(1),

    CONSTRAINT pk_enrollments PRIMARY KEY (user_id, course_id),

    -- 外键，以确保数据完整性
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (course_id) REFERENCES Courses(course_id)
);

验证

设置复合主键后，数据库将强制执行 (user_id, course_id) 对的唯一性。让我们通过尝试插入重复的注册来测试这一点。

-- 假设用户 101 和课程 202 存在。
-- 第一次插入（成功）
INSERT INTO Enrollments (user_id, course_id, enrollment_date) VALUES (101, 202, '2023-10-27');
-- Query OK, 1 row affected.

-- 第二次插入相同组合（失败）
INSERT INTO Enrollments (user_id, course_id, enrollment_date) VALUES (101, 202, '2023-10-28');
-- ERROR:  duplicate key value violates unique constraint "pk_enrollments"
-- DETAIL: Key (user_id, course_id)=(101, 202) already exists.

错误消息证实了复合主键正在按预期工作，阻止了重复注册。

删除复合主键

要删除复合主键，你需要使用 ALTER TABLE 语句。具体的语法在不同的数据库系统之间略有不同，这就是为什么命名约束如此重要的原因。

标准 SQL / PostgreSQL / SQL Server

这些系统使用约束的名称来删除它。

ALTER TABLE Enrollments
DROP CONSTRAINT pk_enrollments;

MySQL

在 MySQL 中，主键总是命名为 PRIMARY，因此你使用 PRIMARY KEY 关键字来删除它。

ALTER TABLE Enrollments
DROP PRIMARY KEY;

复合主键 vs. 代理键

复合主键的替代方案是代理键（Surrogate Key）：一个单独的、自增的整数列（例如，enrollment_id INT AUTO_INCREMENT PRIMARY KEY）。

方面	复合主键 (例如 user_id, course_id)	代理键 (例如 enrollment_id)
意义	键由真实、有意义的数据（自然键）组成。	键没有业务意义；它仅仅作为唯一的 ID 存在。
连接	连接需要匹配多列，这可能稍微复杂一些。	连接更简单，只需匹配单个整数列，这通常更快。
唯一性	保证业务规则的唯一性（例如，每个用户/课程只能注册一次）。	需要对 (user_id, course_id) 添加额外的 UNIQUE 约束来强制执行业务规则。
使用场景	最适合连接表，其中外键组合是自然标识符。	通常用于主实体表（Users、Products）和使用 ORM 时。

这个选择是一个重要的数据库设计决策。许多开发人员和对象关系映射（ORM）框架为了简单和一致性，倾向于为每个表使用代理键，同时仍在自然键列上添加单独的唯一约束以维护数据完整性。

最佳实践

• 命名你的约束：始终为你的键提供一个自定义名称（例如，pk_enrollments），以便将来更容易进行修改。
• 选择稳定的列：复合主键中的列不应随时间变化。
• 考虑索引性能：复合主键会自动创建一个复合索引。键中列的顺序对查询性能很重要。将最常用于筛选的列放在首位。
• 保持精简：避免在复合主键中使用过多的列或宽泛的数据类型（例如长字符串），因为这可能会影响存储和性能。