MySQL插入TripSchedule表外键数据报1452错误如何解决？

问题根源 #

• 外键关联逻辑和存储值不匹配：你TripSchedule表插入的StartLoc、EndLoc值（如L11、L10等）实际是Location表的LocationID字段值，但最初建表时你把外键关联到了Location表的StartLoc、EndLoc字段，后续修改表结构后外键关联到了LocationID，但你插入的部分值在Location表中不存在。
• 关联字段缺失定义：创建TripSchedule表的SQL中，你没有先定义StartLoc、EndLoc、RequestID等字段，直接声明外键约束，属于语法逻辑错误。
• 插入值不存在：你TripSchedule插入语句中从第4条开始的StartLoc值为L17、L19、L21、L23，EndLoc值为L18、L20、L22，但Location表中只有L10到L16的LocationID记录，没有对应值触发外键约束。
• 插入语句语法错误：第一条TripSchedule插入语句中L11前缺失单引号，S1后缺失逗号，会导致插入失败。

具体解决方案 #

1. 修正表结构 #

按照实际业务逻辑，TripSchedule的StartLoc、EndLoc存储的是地点ID，所以外键应该关联Location表的主键LocationID，正确建表语句如下：

-- 先删除已创建的错误表
DROP TABLE IF EXISTS TripSchedule;
DROP TABLE IF EXISTS Location;

-- 重建Location表
CREATE TABLE Location(
    LocationID CHAR(4) PRIMARY KEY,
    LocationName CHAR(20),
    LocationState CHAR(3),
    LocationPostCode CHAR(4),
    StartLoc CHAR(3),
    EndLoc CHAR(3)
);

-- 重建TripSchedule表，先定义字段再设置外键
CREATE TABLE TripSchedule(
    ScheduleID CHAR(3),
    StartLoc CHAR(4), -- 要和关联的LocationID字段类型长度完全一致
    EndLoc CHAR(4),
    RequestID CHAR(3),
    TruckVINNum CHAR(4),
    TransportID CHAR(2),
    StaffID CHAR(2),
    TripStart CHAR(40),
    TripEnd CHAR(40),
    -- 外键约束
    FOREIGN KEY(StartLoc) REFERENCES Location(LocationID),
    FOREIGN KEY(EndLoc) REFERENCES Location(LocationID),
    FOREIGN KEY(RequestID) REFERENCES BookingReq(RequestID),
    FOREIGN KEY(TruckVINNum) REFERENCES Allocation(TruckVINNum),
    FOREIGN KEY(TransportID) REFERENCES Allocation(TransportID),
    FOREIGN KEY(StaffID) REFERENCES Staff(StaffID)
);

2. 补全Location表数据 #

插入TripSchedule需要的L17到L23对应的Location记录，示例：

INSERT INTO Location VALUES ('L17','示例地点1','TAS','7001','L45','L44');
INSERT INTO Location VALUES ('L18','示例地点2','TAS','7002','L47','L46');
-- 依次补全L19到L23的所有记录

3. 修正插入语句的语法错误 #

修正第一条插入语句的缺失符号问题：

-- 修正前错误：insert into TripSchedule values('S23',L11','L10', 'R101', 'VO20','T1','S1''6th of December 2020-1:30pm','7th of December 2021-1:30 pm');
-- 修正后
INSERT INTO TripSchedule VALUES('S23','L11','L10', 'R101', 'VO20','T1','S1','6th of December 2020-1:30pm','7th of December 2021-1:30 pm');

临时测试方案（不建议生产环境使用） #

如果仅需要临时测试插入数据，可以先关闭外键检查，插入完成后再开启：

SET FOREIGN_KEY_CHECKS = 0;
-- 执行插入语句
SET FOREIGN_KEY_CHECKS = 1;

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