錯誤1452MySQL-無法添加或更新子行

我正在嘗試將我的資料插入到我創建的表中，我收到以下兩個錯誤。我不太明白這些錯誤試圖告訴我在我的資料中修復或更改什么。有沒有人能夠指出我正確的方向，哪些資料會導致這些錯誤出現。我將在錯誤下方附上我的代碼。謝謝大家。

錯誤代碼：1452。無法添加或更新子行：外鍵約束失敗 ( meraki. truck, CONSTRAINT truck_ibfk_1FOREIGN KEY ( TruckMakeID, TruckModelID) REFERENCES truckmodel( TruckMakeID, TruckModelID))

錯誤代碼：1452。無法添加或更新子行：外鍵約束失敗 ( meraki. allocation, CONSTRAINT allocation_ibfk_1FOREIGN KEY ( TruckVINNum) REFERENCES truck( TruckVINNum))

代碼如下

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

uj5u.com熱心網友回復：

當您嘗試向定義了外鍵的表中插入值時，您需要確保您為外鍵列輸入的特定值在父表中可用。

在您的情況下，當將值插入TruckMake和TruckModel表時，對于TruckMakeID列，您使用的是帶有前綴的值TMI。但是，當將值插入Truck表中時，對于列TruckMakeID，您使用的是帶有前綴TM(I丟失) 的值。這就是錯誤的原因，因為表TM中沒有前綴后跟數字的值TruckModel。

由于資料未添加到 Truck 表中，因此在嘗試將值插入Allocation表時會出現錯誤，因為定義了TruckVINNum從Truck表中參考的外鍵。

標籤：mysql 数据库

上一篇：比較和匹配鍵系結，同時比較2個表并回傳計數

下一篇：如何架構我的支付網關資料庫