I am trying to insert my data into my created tables I get these two errors as follows. I don't quite understand what these errors are trying to tell me to fix or change within my data. Would anyone be able to point me in the right direction to which data would be causing these errors to appear. I will attach my code below the errors. Thank you everyone.
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (
meraki
.truck
, CONSTRAINTtruck_ibfk_1
FOREIGN KEY (TruckMakeID
,TruckModelID
) REFERENCEStruckmodel
(TruckMakeID
,TruckModelID
))
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (
meraki
.allocation
, CONSTRAINTallocation_ibfk_1
FOREIGN KEY (TruckVINNum
) REFERENCEStruck
(TruckVINNum
))
The code is as follows
CREATE DATABASE meraki;
- List item
USE meraki;
create table TruckMake(
TruckMakeID char(5),
TruckMakeName varchar(20),
primary key(TruckMakeID));
-- create Truckmodel
create table TruckModel(
TruckMakeID char(5),
TruckModelID char(4),
TruckModelName char(4),
primary key(TruckMakeID, TruckModelID),
foreign key(TruckMakeID) references TruckMake(TruckMakeID));
-- create Truck
create table Truck(
TruckVINNum varchar(4),
TruckMakeID char(3),
TruckModelID char(3),
TruckColour varchar(25),
TruckPurchaseDate varchar(40),
TruckCost varchar(25),
primary key (TruckVINNum),
foreign key (TruckMakeID,TruckModelID) references TruckModel(TruckMakeID,TruckModelID));
-- create Service
create table Service(
TransportID char(2),
TransportName varchar(20),
TransportCost varchar(20),
TransportMaxDist varchar(15),
primary key (TransportID));
-- create allocation
create table Allocation(
TruckVINNum varchar(4),
TransportID char(3),
FromDate varchar(25),
ToDate varchar(25),
primary key(TruckVINNum,TransportID),
foreign key(TruckVINNum) references Truck(TruckVINNum),
foreign key(TransportID) references Service(TransportID));
-- insert Truck Makes
Insert into TruckMake values ('TMI1','Mercedes');
Insert into TruckMake values ('TMI2','Volvo');
Insert into TruckMake values ('TMI3','Toyota');
Insert into TruckMake values ('TMI4','Subaru');
Insert into TruckMake values ('TMI5','Ford');
Insert into TruckMake values ('TMI6','Ferrari');
Insert into TruckMake values ('TMI7','Bugatti');
Insert into TruckMake values ('TMI8','Pagani');
Insert into TruckMake values ('TMI9','Volvo');
-- insert Truck Models
Insert into TruckModel values ('TMI1','MO1','MA12');
Insert into TruckModel values ('TMI2','MO2','LA17');
Insert into TruckModel values ('TMI3','MO3','LH21');
Insert into TruckModel values ('TMI4','MO4','MJ21');
Insert into TruckModel values ('TMI5','MO5','OY21');
Insert into TruckModel values ('TMI6','MO6','UI12');
Insert into TruckModel values ('TMI7','MO7','LH18');
Insert into TruckModel values ('TMI8','MO8','MH21');
Insert into TruckModel values ('TMI9','MO9','OH12');
-- insert Trucks
Insert into Truck values ('V023','TM1','MO1','Red','3rd of September, 2021','$350000');
Insert into Truck values ('JK01','TM2','MO2','Green','16th of June, 2020','$343582.56');
Insert into Truck values ('LY29','TM3','MO3','Green','12th of July, 2021','$342499.63');
Insert into Truck values ('AB43','TM4','MO4','White','24th of January,
2020','$360000.51');
Insert into Truck values ('LP21','TM5','MO5','White','12th of Feburary,
2021','$354000.25');
Insert into Truck values ('V022','TM6','MO6','Red','3rd of October, 2021','$345000');
Insert into Truck values ('JK04','TM7','MO7','Red','15th of December, 2020','$247522.36');
Insert into Truck values ('LY25','TM8','MO8','Black','20th of January,
2016','$348499.13');
Insert into Truck values ('AB33','TM9','MO9','Black','30th of January,
2019','$220410.31');
-- inset Service
Insert into Service values ('T1','RemovalServices','$7589','500');
Insert into Service values ('T2','Extractor','$6578','1100');
Insert into Service values ('T3','Movalist','$2200','700');
Insert into Service values ('T4','MovementServices','$6859','1220');
Insert into Service values ('T5','Transportalist','$4530','1150');
Insert into Service values ('T6','RemovalServices','$1597','500');
Insert into Service values ('T7','Movalist','$3000','1100');
Insert into Service values ('T8','Extractor','$2200','700');
Insert into Service values ('T9','Transportalist','$6859','1220');
-- insert Allocation
Insert into Allocation values ('V023','T1','1st of October,2020','3rd of October,2020');
Insert into Allocation values ('JK01','T2','2nd of January,2021','5th of January,2021');
Insert into Allocation values ('LY29','T3', '3rd of July,2020','6th of July,2020');
Insert into Allocation values ('AB43','T4','5th of June,2021','8th of June,2021');
Insert into Allocation values ('LP21','T5','7th of Janurary,2020','11th of
January,2020');
Insert into Allocation values ('V022','T6','10th of February,2021','14th of
February,2021');
Insert into Allocation values ('JK04','T7','15th of September,2020','18th of
September,2020');
Insert into Allocation values ('LY25','T8','17th of October,2021','21st of
October,2021');
Insert into Allocation values ('AB33','T9','18th of March,2020','20th of March,2020');
CodePudding user response:
When you are trying to insert values into a table where foreign keys are defined, you need to make sure the specific values you enter for foreign key columns are available in the parent table.
In your case, when inserting values into the TruckMake
and TruckModel
tables, for TruckMakeID
column, you are using values with the prefix TMI
. But when inserting values into the Truck
table, for the column TruckMakeID
, you are using values with the prefix TM
(I
is missing). That's the reason for the error since values with prefix TM
followed by a number are not available in the TruckModel
table.
Since the data is not added to the Truck table, there are errors when trying to insert values to Allocation
table since there is a foreign key defined for TruckVINNum
referred from the Truck
table.