SQL中外键引用复合主键单字段报错及后续问题咨询

问题1：单独用id_student作为外键引用studentInfo时报错的解决方法 #

你遇到的报错原因很明确：外键必须引用被关联表的主键或者候选键（带唯一约束的字段）。而studentInfo的主键是复合主键(id_student, id_module)，单独的id_student在这个表里并不是唯一的——也就是说，同一个学生ID可能对应多个不同的模块ID，数据库无法确定你要关联的是哪一条记录，所以拒绝创建这个外键。

有两种解决思路：

方案1：给studentInfo的id_student添加唯一约束

如果业务上保证每个id_student是唯一的（即使它是复合主键的一部分），可以给这个字段加唯一约束，这样它就成为候选键，就能被外键引用了：

ALTER TABLE studentInfo ADD CONSTRAINT UQ_studentInfo_id_student UNIQUE (id_student);

之后再创建studentRoom表，原来的外键语句就能正常执行了。

方案2：修改外键，引用整个复合主键

如果业务上id_student并不唯一（比如一个学生可以选多个模块），那正确的做法是让studentRoom的外键同时包含id_student和id_module，匹配studentInfo的复合主键：

CREATE TABLE studentRoom ( 
    id_student INT, 
    id_room VARCHAR(45), 
    id_module VARCHAR(45),
    PRIMARY KEY (id_student, id_room), 
    FOREIGN KEY (id_module) REFERENCES module(id_module), 
    FOREIGN KEY (id_student, id_module) REFERENCES studentInfo(id_student, id_module) 
);

问题2：没有id_module字段时，能否引用studentInfo的复合主键？ #

答案是不行。SQL对外键的要求是：外键的字段数量、数据类型、顺序必须和被引用的主键/候选键完全一致。studentInfo的主键是两个字段，你的外键也必须提供这两个字段才能建立关联——数据库需要通过这两个字段唯一确定studentInfo里的一条记录，缺少任何一个都做不到。

所以你必须在studentRoom表中添加id_module字段，才能创建引用studentInfo复合主键的外键，就像上面方案2里写的那样。

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