SQL not allowing to add data into foreign keys

Question

I have ran into an error in MySQL where it will not allow me to enter data for foreign keys. This is my data:

 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');

The issue I'm having is a bit of a weird one, it lies with the relationship between the Location and TripSchedule table. SQL will allow the first three rows of the insert values for the TripSchedule table to be entered, however the rest of the insert values give error: "Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (assessment.tripschedule, CONSTRAINT tripschedule_ibfk_1 FOREIGN KEY (StartLoc) REFERENCES location (LocationID))".

I have tried referencing the StartLoc and EndLoc as primary keys in the Location table and have also tried completely deleting the StartLoc and EndLoc from the Location table but still get the same problem. Is there any solution to this?

your create tables are inconsistent, and don't include all the information; please try to provide the actual table structures, as shown by `show create table yourtablename` when asking questions — ysth, Oct 24 '21 at 11:43

score 1 · Accepted Answer · answered Oct 24 '21 at 11:41

Your foreign key constraint is requiring that locations exist in order to specify them as StartLoc. Your fourth through seventh inserts specify StartLoc as L17, L19, L21, and L23, none of which are LocationIDs that exist.

Either create the Location records before inserting the TripSchedule records that refer to them, or remove your foreign key constraint:

alter table TripSchedule drop constraint tripschedule_ibfk_1;

SQL not allowing to add data into foreign keys

1 Answers1