如何命名复合主键并在关联表中引用该复合主键？

一、正确的复合主键命名方式 #

在MySQL中，给复合主键指定名称的标准语法有两种：

方式1：直接定义主键时指定约束名 #

这是最直观推荐的写法：

CREATE TABLE em (
  pid INT NOT NULL,
  name VARCHAR(10) NOT NULL,
  surname VARCHAR(10) NOT NULL,
  -- 自定义复合主键的约束名pk_em
  CONSTRAINT pk_em PRIMARY KEY (pid, name, surname)
);

方式2：基于索引创建命名主键（可选） #

如果需要先创建索引再将其设为主键，语法如下（不推荐，仅作参考）：

CREATE TABLE em (
  pid INT NOT NULL,
  name VARCHAR(10) NOT NULL,
  surname VARCHAR(10) NOT NULL,
  -- 先创建命名联合索引
  INDEX idx_em_pid_name_surname (pid, name, surname),
  -- 将该索引设为主键并沿用索引名
  PRIMARY KEY USING INDEX idx_em_pid_name_surname
);

二、评论表co引用复合主键的方法 #

外键必须严格匹配父表的全部主键字段，因此需要调整co表结构，添加复合主键对应的所有字段后再创建外键：

1. 新建co表的写法 #

CREATE TABLE co (
  id INT PRIMARY KEY AUTO_INCREMENT,
  -- 对应em表的三个主键字段，命名前缀em_用于区分
  em_pid INT NOT NULL,
  em_name VARCHAR(10) NOT NULL,
  em_surname VARCHAR(10) NOT NULL,
  content VARCHAR(100) NOT NULL,
  -- 创建外键关联em表的复合主键
  CONSTRAINT fk_co_em FOREIGN KEY (em_pid, em_name, em_surname) 
  REFERENCES em(pid, name, surname)
);

2. 修改已有co表的写法 #

如果co表已存在，执行以下步骤：

-- 重命名原em字段为em_pid（更清晰）
ALTER TABLE co RENAME COLUMN em TO em_pid;
-- 添加对应复合主键的另外两个字段
ALTER TABLE co ADD COLUMN em_name VARCHAR(10) NOT NULL;
ALTER TABLE co ADD COLUMN em_surname VARCHAR(10) NOT NULL;
-- 创建外键约束
ALTER TABLE co ADD CONSTRAINT fk_co_em 
FOREIGN KEY (em_pid, em_name, em_surname) 
REFERENCES em(pid, name, surname);

关键注意点 #

• 外键字段的数据类型、长度、非空属性必须和父表主键字段完全一致，否则创建失败。
• 复合主键的字段顺序不可随意调整，外键的字段顺序必须与父表主键顺序完全匹配。

内容的提问来源于stack exchange，提问作者bliako