MySQL 外键约束 - 错误 1452 - 无法添加或更新子行

MySQL Foreign key constraint - Error 1452 - Cannot add or update child row

提问by Calvin

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:

笔记：

1. It happens when I either INSERTor UPDATEinto tblOrderItems.
2. tblCatalogItemsdoeshave an ItemIDof 0004. See: this

1. 当我INSERT或UPDATE进入tblOrderItems.
2. tblCatalogItems确实有一个ItemID的0004。见：这个

Here are the create statements generated by MySQL Workbench:

以下是 MySQL Workbench 生成的 create 语句：

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 thisrelevant 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.

我尝试了这篇相关帖子中的建议，但没有结果。这是一个尚未实际使用的新数据库；我刚刚用假数据填充了它。任何想法将不胜感激。

回答by Joachim Isaksson

There is a foreign key constraint on tblOrderItemsthat its ItemIDneeds to reference an ItemIDthat already exists in tblCatalogItems.

有一个外键约束tblOrderItems，它ItemID需要引用ItemID已经存在于tblCatalogItems.

CONSTRAINT `ItemID` FOREIGN KEY (`ItemID`) REFERENCES `tblCatalogItems` (`ItemID`),

The message only means that you're trying to update tblOrderItemsto reference the item in tblCatalogItemswith ItemID= 0004, but that item does not exist.

该消息仅表示您正在尝试更新tblOrderItems以引用tblCatalogItemswith ItemID= 0004 中的项目，但该项目不存在。

Since ItemIDis a varchar, you probably want to quote the 0004or it may be converted to an int 4before conversion to varchar. That may be your problem if the row with ItemID= 0004 actually exists.

由于ItemID是 varchar，您可能希望引用 the0004或者4在转换为 varchar 之前将其转换为 int 。如果ItemID= 0004的行确实存在，那可能是您的问题。

UPDATE tblOrderItems SET `ItemID` = '0004' WHERE `OrderNum`= 203 AND `OrderItemID` = 26

回答by Chamila Chulatunga

Without seeing the table data I can't be sure, but I'm guessing it's because there is no record in tblCatalogItemswith an ItemIDof '0004'.

在没有看到表中的数据我不能肯定，但我猜这是因为在没有记录tblCatalogItems与ItemID的“0004”。

Also, you probably need to quote the 0004in your update statement since the column is defined as character (varchar(10)) not number (int).

此外，您可能需要0004在更新语句中引用 the ，因为该列被定义为字符 ( varchar(10)) 而不是数字 ( int)。

There is a foreign key relationship defined between tblCatalogItems.ItemIdand tblOrderItems.ItemIdwhich means that any row in tblOrderItemscan only have a value for ItemIdthat matches an ItemIdfound in tblCatalogItems.

在tblCatalogItems.ItemId和之间定义了外键关系，tblOrderItems.ItemId这意味着中的任何行tblOrderItems只能具有ItemId与 中ItemId找到的匹配的值tblCatalogItems。

You would therefore need to insert a record into 'tblCatalogItems' with an ItemIdof '0004' first, before running your update on tblOrderItems

因此ItemId，在运行更新之前，您需要先将记录插入到 'tblCatalogItems' 中，其值为 '0004'tblOrderItems

Alternatively you need to change the SET ItemID =clause in your update to set a value that matches to an ItemIdvalue that actually exists in the tblCatalogItemstable

或者，您需要更改SET ItemID =更新中的子句以设置与表ItemId中实际存在的值匹配的值tblCatalogItems

回答by troelskn

The data type needs to match on both sides of your foreign key constraint. Here you have a varchar(50)referring a varchar(10), which isn't allowed.

数据类型需要在外键约束的两侧匹配。这里有一个varchar(50)引用 a varchar(10)，这是不允许的。

CREATE TABLE `tblCatalogItems` (
  `ItemID` varchar(10) NOT NULL DEFAULT '',
  ...
) ENGINE=InnoDB DEFAULT CHARSET=latin1$$

...

CREATE TABLE `tblOrderItems` (
  `ItemID` varchar(50) DEFAULT NULL,
  ...
  CONSTRAINT `UnitPrice` FOREIGN KEY (`ItemID`) REFERENCES `tblCatalogItems` (`ItemID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=7678 DEFAULT CHARSET=latin1$$