MySQL Foreign key constraint - Error 1452 - Cannot add or update child row
There is a foreign key constraint on tblOrderItems
that its ItemID
needs to reference an ItemID
that already exists in tblCatalogItems
.
CONSTRAINT `ItemID` FOREIGN KEY (`ItemID`) REFERENCES `tblCatalogItems` (`ItemID`),
The message only means that you're trying to update tblOrderItems
to reference the item in tblCatalogItems
with ItemID
= 0004, but that item does not exist.
Since ItemID
is a varchar, you probably want to quote the 0004
or it may be converted to an int 4
before conversion to varchar. That may be your problem if the row with ItemID
= 0004 actually exists.
UPDATE tblOrderItems SET `ItemID` = '0004' WHERE `OrderNum`= 203 AND `OrderItemID` = 26
calvin
Updated on July 09, 2022Comments
-
calvin almost 2 years
I've used the other posts on this topic, but I'm having no luck.
Here's the code I execute:
UPDATE tblOrderItems SET `ItemID` = 0004 WHERE `OrderNum`= 203 AND `OrderItemID` = 26
Here's my error:
Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`cai0066`.`tblOrderItems`, CONSTRAINT `ItemID` FOREIGN KEY (`ItemID`) REFERENCES `tblCatalogItems` (`ItemID`))
Notes:
- It happens when I either
INSERT
orUPDATE
intotblOrderItems
. -
tblCatalogItems
does have anItemID
of0004
. See: this
Here are the create statements generated by MySQL Workbench:
delimiter $$ CREATE TABLE `tblCatalogItems` ( `ItemID` varchar(10) NOT NULL DEFAULT '', `ItemName` varchar(50) DEFAULT NULL, `Wholesale` decimal(10,2) DEFAULT NULL, `Cost5-10` decimal(10,2) DEFAULT NULL, `Cost11-19` decimal(10,2) DEFAULT NULL, `Cost20` decimal(10,2) DEFAULT NULL, `Retail` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`ItemID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ delimiter $$ CREATE TABLE `tblItemCosts` ( `Cost` decimal(10,2) DEFAULT NULL, `VendorID` int(11) NOT NULL, `ItemID` varchar(10) NOT NULL, KEY `VendorID_idx` (`VendorID`), KEY `ItemID_idx` (`ItemID`), CONSTRAINT `VendorID` FOREIGN KEY (`VendorID`) REFERENCES `tblVendors` (`VendorID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ delimiter $$ CREATE TABLE `tblOrderItems` ( `OrderItemID` int(11) NOT NULL AUTO_INCREMENT, `OrderNum` int(11) NOT NULL, `PayPalTxnID` int(10) DEFAULT NULL, `Description` varchar(225) DEFAULT NULL, `Quantity` int(11) DEFAULT NULL, `UnitPrice` decimal(10,2) DEFAULT NULL, `ItemStatus` varchar(30) DEFAULT NULL, `TrackingNumber` varchar(50) DEFAULT NULL, `ShippingCost` decimal(10,2) DEFAULT NULL, `ItemID` varchar(50) DEFAULT NULL, `TotalPrice` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`OrderItemID`,`OrderNum`), UNIQUE KEY `PayPalTxnID_UNIQUE` (`PayPalTxnID`), KEY `PayPalTxnID_idx` (`PayPalTxnID`), KEY `UnitPrice_idx` (`ItemID`), KEY `OrderNum_idx` (`OrderNum`), CONSTRAINT `ItemID` FOREIGN KEY (`ItemID`) REFERENCES `tblCatalogItems` (`ItemID`), CONSTRAINT `OrderNum` FOREIGN KEY (`OrderNum`) REFERENCES `tblOrders` (`OrderNum`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `UnitPrice` FOREIGN KEY (`ItemID`) REFERENCES `tblCatalogItems` (`ItemID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB AUTO_INCREMENT=7678 DEFAULT CHARSET=latin1$$ delimiter $$ CREATE TABLE `tblOrderItemStatus` ( `OrderItemID` int(11) NOT NULL, `OrderDate` varchar(12) DEFAULT NULL, `DesignProofSent` varchar(12) DEFAULT NULL, `SubmittedToProduction` varchar(12) DEFAULT NULL, `InProduction` varchar(12) DEFAULT NULL, `Shipped` varchar(12) DEFAULT NULL, PRIMARY KEY (`OrderItemID`), UNIQUE KEY `OrderItemID_UNIQUE` (`OrderItemID`), KEY `OrderItemID_idx` (`OrderItemID`), CONSTRAINT `OrderItemID` FOREIGN KEY (`OrderItemID`) REFERENCES `tblOrderItems` (`OrderItemID`) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$ delimiter $$ CREATE TABLE `tblOrders` ( `OrderNum` int(11) NOT NULL AUTO_INCREMENT, `PayPalTxnID` int(10) DEFAULT NULL, `OrderDate` varchar(50) DEFAULT NULL, `OrderStatus` varchar(10) DEFAULT 'New', `RushFlag` bit(1) DEFAULT b'0', `ShipName` varchar(50) DEFAULT NULL, `ShipEmail` varchar(100) DEFAULT NULL, `ShipAddress1` varchar(50) DEFAULT NULL, `ShipAddress2` varchar(50) DEFAULT NULL, `ShipCity` varchar(50) DEFAULT NULL, `ShipState` char(2) DEFAULT NULL, `ShipZip` varchar(10) DEFAULT NULL, `ShippingCharge` decimal(10,2) DEFAULT NULL, `TotalCost` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`OrderNum`), UNIQUE KEY `PayPalTxnID_UNIQUE` (`PayPalTxnID`) ) ENGINE=InnoDB AUTO_INCREMENT=346 DEFAULT CHARSET=latin1$$ delimiter $$ CREATE TABLE `tblVendors` ( `VendorID` int(11) NOT NULL, `VendorName` varchar(50) DEFAULT NULL, PRIMARY KEY (`VendorID`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1$$
I tried the suggestion in this relevant post, but there were no results. This is a new database that hasn't actually been used yet; I've just filled it with fake data. Any ideas would be greatly appreciated.
- It happens when I either
-
calvin over 11 yearsI just threw off my glasses because I've been working on this all day and it was a type issue. Thank you. Some of the
ItemID
s have chars in them, so I'll just have to quote them all. Thanks again!