如何用命令建立复合主键？具体操作步骤是什么？

复合主键是由数据库表中两个或多个列组合而成的主键,用于唯一标识表中的每一行记录，当单个列无法保证唯一性时（例如订单表中，同一订单可能包含多个商品，需通过订单ID和商品ID共同标识唯一记录），复合主键就显得尤为重要，下面将详细介绍在不同主流数据库管理系统中（如MySQL、PostgreSQL、SQL Server、Oracle），如何通过命令建立复合主键，包括创建表时指定、修改已有表添加等场景，并辅以示例和注意事项说明。

MySQL 中建立复合主键

MySQL 是最常用的开源关系型数据库，支持在创建表时或通过修改表结构的方式定义复合主键。

创建表时指定复合主键

在 CREATE TABLE 语句中，通过 PRIMARY KEY (列1, 列2, ..., 列n) 语法直接指定多个列作为复合主键，创建一个订单详情表（order_items），包含订单ID（order_id）、商品ID（product_id）、购买数量（quantity）等字段，其中订单ID和商品ID需共同作为主键：

CREATE TABLE order_items (
    order_id INT NOT NULL,
    product_id INT NOT NULL,
    quantity INT DEFAULT 1,
    unit_price DECIMAL(10, 2),
    PRIMARY KEY (order_id, product_id)  -- 指定 order_id 和 product_id 为复合主键
);

修改已有表添加复合主键

若表已存在且未定义主键,可通过 ALTER TABLE 语句添加复合主键，语法为：ALTER TABLE 表名 ADD PRIMARY KEY (列1, 列2, ..., 列n)，为已存在的 order_items 表添加复合主键：

ALTER TABLE order_items ADD PRIMARY KEY (order_id, product_id);

注意事项

• 复合主键中的所有列均不能包含 NULL 值（MySQL 会隐式添加 NOT NULL 约束）。
• 复合主键的列顺序会影响索引结构,通常将高选择性（区分度高）的列放在前面。
• 若表中已存在重复数据（同一订单ID和商品ID的多条记录），添加主键会失败，需先清理重复数据。

PostgreSQL 中建立复合主键

PostgreSQL 作为功能强大的开源数据库，其复合主键的创建语法与 MySQL 类似，但支持更丰富的约束选项。

创建表时指定复合主键

使用 PRIMARY KEY (列1, 列2) 语法，同时可结合 NOT NULL 约束（复合主键本身隐含非空），创建员工项目分配表（employee_projects）：

CREATE TABLE employee_projects (
    employee_id INT NOT NULL,
    project_id INT NOT NULL,
    role VARCHAR(50),
    start_date DATE,
    PRIMARY KEY (employee_id, project_id)  -- 员工ID和项目ID作为复合主键
);

修改已有表添加复合主键

通过 ALTER TABLE 语句添加，语法为：ALTER TABLE 表名 ADD PRIMARY KEY (列1, 列2)。

ALTER TABLE employee_projects ADD PRIMARY KEY (employee_id, project_id);

使用命名约束（可选）

PostgreSQL 允许为复合主键指定自定义约束名，便于管理：

ALTER TABLE employee_projects 
ADD CONSTRAINT pk_employee_projects PRIMARY KEY (employee_id, project_id);

注意事项

• 复合主键会自动创建唯一索引,可通过 EXPLAIN 命令验证索引是否生效。
• 若列已定义 NULL 值，需先修改为 NOT NULL 再添加主键，否则会报错。

SQL Server 中建立复合主键

SQL Server 是微软的关系型数据库，其复合主键的创建需显式指定约束名称（与 MySQL、PostgreSQL 不同）。

创建表时指定复合主键

在 CREATE TABLE 语句中，通过 CONSTRAINT 约束名 PRIMARY KEY (列1, 列2) 语法定义，创建库存表（inventory）：

CREATE TABLE inventory (
    product_id INT NOT NULL,
    warehouse_id INT NOT NULL,
    stock_quantity INT DEFAULT 0,
    last_updated DATETIME DEFAULT GETDATE(),
    CONSTRAINT PK_inventory_product_warehouse PRIMARY KEY (product_id, warehouse_id)  -- 指定约束名和复合主键列
);

修改已有表添加复合主键

使用 ALTER TABLE 语句时，必须指定约束名，语法为：ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (列1, 列2)。

ALTER TABLE inventory ADD CONSTRAINT PK_inventory_product_warehouse PRIMARY KEY (product_id, warehouse_id);

删除并重建复合主键（若需修改）

若需修改复合主键,需先删除原约束，再添加新约束：

-- 删除原主键约束
ALTER TABLE inventory DROP CONSTRAINT PK_inventory_product_warehouse;
-- 添加新的复合主键（例如增加批次号）
ALTER TABLE inventory ADD CONSTRAINT PK_inventory_product_warehouse_batch PRIMARY KEY (product_id, warehouse_id, batch_number);

注意事项

• SQL Server 要求主键约束名必须唯一，且需符合标识符命名规则。
• 复合主键的列顺序会影响查询性能,建议将高基数的列（如商品ID）放在前面。

Oracle 中建立复合主键

Oracle 作为企业级数据库，其复合主键的创建语法与 SQL Server 类似，需指定约束名，且对数据类型和空值有严格要求。

创建表时指定复合主键

语法为：CONSTRAINT 约束名 PRIMARY KEY (列1, 列2)，创建课程选课表（course_enrollments）：

CREATE TABLE course_enrollments (
    student_id NUMBER(10) NOT NULL,
    course_id VARCHAR(10) NOT NULL,
    enrollment_date DATE DEFAULT SYSDATE,
    grade CHAR(2),
    CONSTRAINT PK_course_enrollments_student_course PRIMARY KEY (student_id, course_id)  -- 学生ID和课程ID作为复合主键
);

修改已有表添加复合主键

通过 ALTER TABLE 语句添加，需指定约束名：

ALTER TABLE course_enrollments ADD CONSTRAINT PK_course_enrollments_student_course PRIMARY KEY (student_id, course_id);

使用 ALTER TABLE 修改列属性（前置条件）

若列包含 NULL 值，需先修改为 NOT NULL，否则添加主键会失败：

ALTER TABLE course_enrollments MODIFY student_id NUMBER(10) NOT NULL;
ALTER TABLE course_enrollments MODIFY course_id VARCHAR(10) NOT NULL;

注意事项

• Oracle 的复合主键会自动创建唯一索引，可通过 USER_INDEXES 视图查询索引信息。
• 复合主键的列总长度（字节）需符合 Oracle 的限制（通常不超过 32KB）。

不同数据库复合主键语法对比

为便于快速查阅,以下表格总结了主流数据库创建复合主键的核心语法差异：

建立复合主键的注意事项

1. 字段选择原则：

   • 选择业务相关且稳定的列（如订单ID、商品ID），避免使用频繁变动的列（如用户昵称）。
   • 确保组合列的值具有唯一性,可通过 SELECT 列1, 列2, COUNT(*) FROM 表名 GROUP BY 列1, 列2 HAVING COUNT(*) > 1 检查重复数据。

2. 性能影响：

   

   • 复合主键会自动创建唯一索引,查询时需遵循“最左前缀原则”（如复合主键为 (A, B)，则查询 A 或 A+B 可利用索引，单独查询 B 无法利用）。
   • 避免过长的复合主键（超过3列），否则会降低索引效率。

3. 与外键的关系：

   若其他表需引用该表的主键,外键必须引用复合主键的所有列（订单详情表引用订单表时，需同时引用订单ID和商品ID）。

4. NULL 值处理：

   • 复合主键的所有列均不能为 NULL，数据库会自动强制此约束，无需手动添加 NOT NULL（除 Oracle 需显式修改外）。

相关问答FAQs

问题1：复合主键和唯一索引（Unique Index）有什么区别？
解答：复合主键和唯一索引都要求列值唯一，但存在核心区别：

• 约束性质：主键是表级别的约束，要求列值唯一且非空；唯一索引是索引级别的约束，仅要求列值唯一，允许 NULL 值（且多个 NULL 值不视为重复）。
• 数量限制：一个表只能有一个主键（单列或复合），但可以有多个唯一索引。
• 语义含义：主键用于唯一标识表记录，具有业务意义；唯一索引主要用于提升查询性能，约束非核心字段的唯一性。

问题2：如何修改或删除已存在的复合主键？
解答：不同数据库操作略有差异，核心步骤均为“删除原约束+添加新约束”：

• MySQL/PostgreSQL：
  删除：ALTER TABLE 表名 DROP PRIMARY KEY;
  添加：ALTER TABLE 表名 ADD PRIMARY KEY (列1, 列2);（MySQL）或 ALTER TABLE 表名 ADD CONSTRAINT 约束名 PRIMARY KEY (列1, 列2);（PostgreSQL）

• SQL Server/Oracle：
  删除：ALTER TABLE 表名 DROP CONSTRAINT 约束名;（需提前知道约束名，可通过 SELECT CONSTRAINT_NAME FROM USER_CONSTRAINTS WHERE TABLE_NAME = '表名' AND CONSTRAINT_TYPE = 'P' 查询）
  添加：ALTER TABLE 表名 ADD CONSTRAINT 新约束名 PRIMARY KEY (列1, 列2);

注意：修改复合主键前需确保新列组合满足唯一性且无 NULL 值，否则会报错。