SQL不允许将数据添加到外键中[重复]

Question

我在MySQL中遇到了一个错误，它不允许我输入外键数据。这是我的数据：

 create table Location(
LocationID char(4) primary key,
LocationName char(20),
LocationState char(3),
LocationPostCode char(4),
StartLoc char(3),
EndLoc char(3)
);
drop table Location;
insert into Location values ('L10','Norwood','TAS','7250','L31','L30');
insert into Location values ('L11','Cressy','TAS','7586','L33','L32');
insert into Location values ('L12','Launceston','TAS','7907','L35','L34');
insert into Location values ('L13','Epping Forest','TAS','7354','L37','L36');
insert into Location values ('L14','Avoca','TAS','7812','L39','L38');
insert into Location values ('L15','Royal George','TAS','7009','L41','L40');
insert into Location values ('L16','Westbury','TAS','7893','L43','L42');

create table TripSchedule(
ScheduleID char(3),
foreign key(StartLoc) references Location(StartLoc),
foreign key(EndLoc) references Location(EndLoc),
foreign key(RequestID) references BookingReq(RequestID),
foreign key(TruckVINNum) references Allocation(TruckVINNum),
foreign key(TransportID) references Allocation(TransportID),
foreign key(StaffID) references Staff(StaffID),
TripStart char(40),
TripEnd char(40)
);

drop table TripSchedule;
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('S24', 'L13','L12', 'R102', 'VO20','T1','S1','6th of December 2020-1:30pm','7th of December, 2021-1:30 pm');
insert into TripSchedule values('S25', 'L15','L14', 'R103', 'VO20','T1','S1','6th of December 2020-1:30pm','7th of December, 2021-1:30 pm');
insert into TripSchedule values('S26', 'L17','L16', 'R104', 'VO20','T1','S1','6th of December 2020-1:30pm','7th of December, 2021-1:30 pm');
insert into TripSchedule values('S27', 'L19','L18', 'R105', 'VO20','T1','S1','6th of December 2020-1:30pm','7th of December, 2021-1:30 pm');
insert into TripSchedule values('S28', 'L21','L20', 'R101', 'VO20','T1','S1','6th of December 2020-1:30pm','7th of December, 2021-1:30 pm');
insert into TripSchedule values('S29', 'L23','L22', 'R106', 'VO20','T1','S1','6th of December 2020-1:30pm','7th of December, 2021-1:30 pm');

我遇到的问题有点奇怪，它与位置和行程表之间的关系有关。SQL将允许输入TripSchedule表的前三行插入值，但是其余的插入值给出错误:“错误代码:1452。无法添加或更新子行:外键约束失败(assessment.tripschedule，CONSTRAINT tripschedule_ibfk_1外键(StartLoc)引用位置(LocationID))。

我曾尝试将StartLoc和EndLoc引用为Location表中的主键，也曾尝试从Location表完全删除StartLoc与EndLoca，但仍然遇到同样的问题。有什么解决办法吗？

Answer 1

外键约束要求位置存在，以便将其指定为StartLoc。第四次到第七次插入将StartLoc指定为L17、L19、L21和L23，这些都不是存在的LocationID。

在插入引用位置记录的Trip附表记录之前创建位置记录，或者删除外键约束：

alter table TripSchedule drop constraint tripschedule_ibfk_1;