创建LessonSchedule表时外键约束不兼容错误求助

错误原因分析 #

触发ERROR 3780 (HY000)的核心问题是外键列与关联主键列的数据类型不匹配：

• Horse表的ID为SMALLINT UNSIGNED，但你定义的LessonSchedule.HorseID是SMALLINT（有符号类型）
• Student表的ID为SMALLINT UNSIGNED，LessonSchedule.StudentID同样是SMALLINT（有符号类型）
  MySQL要求外键列和关联主键列的类型（包括符号属性）必须完全一致，否则会触发类型不兼容报错。

同时原代码还存在两处不符合需求的问题：

1. 主键定义错误：需求明确主键为(HorseID, LessonDateTime)，原代码写成了(HorseID, StudentID, LessonDateTime)
2. 缺失外键删除行为配置：需求要求删除Horse行时自动删除对应LessonSchedule记录，删除Student行时将对应StudentID设为NULL，原代码未配置相关规则

修正后的完整SQL代码 #

-- 已存在的Horse和Student表（无需修改）
CREATE TABLE Horse (
    ID              SMALLINT UNSIGNED AUTO_INCREMENT,
    RegisteredName  VARCHAR(15),
    PRIMARY KEY (ID)
);

CREATE TABLE Student (
    ID             SMALLINT UNSIGNED AUTO_INCREMENT,
    FirstName      VARCHAR(20),
    LastName        VARCHAR(30),
    PRIMARY KEY (ID)
);

-- 符合所有需求的LessonSchedule表创建语句
CREATE TABLE LessonSchedule (
    HorseID SMALLINT UNSIGNED NOT NULL,
    StudentID SMALLINT UNSIGNED NULL,
    LessonDateTime DATETIME NOT NULL,
    PRIMARY KEY (HorseID, LessonDateTime),
    FOREIGN KEY (HorseID) REFERENCES Horse(ID) ON DELETE CASCADE,
    FOREIGN KEY (StudentID) REFERENCES Student(ID) ON DELETE SET NULL
);

关键修正点说明 #

• 类型匹配：将HorseID和StudentID统一改为SMALLINT UNSIGNED，与关联表主键类型完全一致，解决外键兼容问题
• 主键调整：将主键改为(HorseID, LessonDateTime)，严格符合需求
• 外键行为配置：
  • 给Horse的外键添加ON DELETE CASCADE，实现删除Horse行时自动删除对应LessonSchedule记录
  • 给Student的外键添加ON DELETE SET NULL，实现删除Student行时将对应LessonSchedule的StudentID设为NULL

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