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.
There is a foreign key constraint on
tblOrderItems
that itsItemID
needs to reference anItemID
that already exists intblCatalogItems
.The message only means that you're trying to update
tblOrderItems
to reference the item intblCatalogItems
withItemID
= 0004, but that item does not exist.Since
ItemID
is a varchar, you probably want to quote the0004
or it may be converted to an int4
before conversion to varchar. That may be your problem if the row withItemID
= 0004 actually exists.