MySQL中外键定义报错：键引用与表引用不匹配问题排查

问题场景 #

创建以下表结构时触发外键错误：

CREATE TABLE Course (
    Cname VARCHAR(255),
    Department VARCHAR(255),
    PRIMARY KEY (Cname, Department)
); 

CREATE TABLE Student (
    student_id VARCHAR(255) PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    student_email VARCHAR(255),
    student_password VARCHAR(255),
    enrollment_number INT,
    current_year INT,
    branch VARCHAR(255),
    course VARCHAR(255),
    profile_photo BLOB,
    FOREIGN KEY (course) REFERENCES Course(Cname, Department)
);

错误信息：

Error Code: 1239. Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match

错误原因 #

Course表的主键是复合主键（Cname, Department），要求外键必须用相同数量的字段关联，但Student表仅用单个字段course尝试关联两个主键字段，字段数量不匹配，导致外键定义无效。

同时从插入语句能看出业务逻辑存在偏差：原插入中Student的course字段填的是'NA'，实际对应Course表的Department值，混淆了课程名称和院系的存储逻辑。

修复方案 #

1. 修改表结构，添加复合外键 #

调整Student表，添加department字段，用(course, department)作为复合外键关联Course的复合主键：

CREATE TABLE Course (
    Cname VARCHAR(255),
    Department VARCHAR(255),
    PRIMARY KEY (Cname, Department)
); 

CREATE TABLE Student (
    student_id VARCHAR(255) PRIMARY KEY,
    first_name VARCHAR(255),
    last_name VARCHAR(255),
    student_email VARCHAR(255),
    student_password VARCHAR(255),
    enrollment_number INT,
    current_year INT,
    branch VARCHAR(255),
    course VARCHAR(255),
    department VARCHAR(255), -- 添加对应Course的Department字段
    profile_photo BLOB,
    -- 定义复合外键，字段顺序需与Course主键一致
    FOREIGN KEY (course, department) REFERENCES Course(Cname, Department)
);

2. 修正插入语句 #

根据调整后的表结构，修正Student的插入逻辑，确保course和department对应Course表的主键值：

INSERT INTO Course (Cname, Department) VALUES
('MCA', 'NA'),
('BTECH', 'CSE'),
('BTECH', 'CIVIL'),
('BTECH', 'MECHANICAL'),
('BTECH', 'ELECTRICAL');

INSERT INTO Student (student_id, first_name, last_name, student_email, student_password, enrollment_number, current_year, branch, course, department, profile_photo) 
VALUES
('2320403214', 'Vivek', 'Ojha', 'ojhavivek24@gmail.com', '123', 2023, 1, 'MCA', 'MCA', 'NA', 'photo.png');

注意：student_id定义为VARCHAR类型，插入时需加引号；course填'MCA'（对应Course的Cname），department填'NA'（对应Course的Department）。

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