MySQL 错误 1451：1451：无法删除或更新父行：外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26696534/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails
提问by Fasev Moweasck
I have been searching for this error and stumbled upon a few questions of the same nature, but as i understand it, they seem to be concerned on UPDATING issue. Mine stems from DELETING of an entry.
我一直在寻找这个错误并偶然发现了一些相同性质的问题，但据我所知，他们似乎关注更新问题。我的源于删除条目。
Here's how my table is made of:
这是我的桌子的组成方式：
CREATE TABLE `product` (
`product_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT COMMENT
'represents unique identifier for every existing products',
`code` varchar(20) NOT NULL,
`name` varchar(45) NOT NULL COMMENT 'description',
`price` decimal(11,4) NOT NULL,
`short_name` varchar(10) NOT NULL COMMENT
'name that can be used quickly to referenc or immediately know what is the product',
`count` bigint(19) unsigned NOT NULL DEFAULT '0',
`product_type_id` smallint(5) unsigned NOT NULL DEFAULT '0',
`is_active` bit(1) NOT NULL DEFAULT b'0',
PRIMARY KEY (`product_id`),
KEY `product_product_typeFK_idx` (`product_type_id`),
CONSTRAINT `product_product_typeFK` FOREIGN KEY (`product_type_id`) REFERENCES
`product_type` (`product_type_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
Then it also has some accompanying TRIGGERwhich inserts some data:
然后它还有一些伴随TRIGGER插入一些数据：
USE `RFVPOS`;
DELIMITER $$
CREATE TRIGGER `Product_BDEL` BEFORE DELETE ON `product` FOR EACH ROW
BEGIN
INSERT INTO `product_audit`
(product_id,
code,
name,
short_name,
price,
count,
delete_user,
delete_date
)
values
(OLD.product_id,
OLD.code,
OLD.name,
OLD.short_name,
OLD.price,
OLD.count,
CURRENT_USER(),
NOW()
);
END
Here as well is the structure of 'product_audit':
这里也是“product_audit”的结构：
CREATE TABLE `product_audit` (
`product_audit_id` mediumint(8) unsigned NOT NULL AUTO_INCREMENT,
`product_id` mediumint(8) unsigned NOT NULL,
`code` varchar(20) NOT NULL,
`name` varchar(45) NOT NULL,
`price` decimal(11,4) NOT NULL,
`short_name` varchar(10) NOT NULL,
`count` bigint(19) unsigned NOT NULL,
`delete_user` varchar(45) NOT NULL,
`delete_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`product_audit_id`),
KEY `product_audit_productFK_idx` (`product_id`),
CONSTRAINT `product_audit_productFK` FOREIGN KEY (`product_id`) REFERENCES `product` (`product_id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB AUTO_INCREMENT=17 DEFAULT CHARSET=utf8;
Then it flashes this error:
然后它闪烁这个错误：
ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails
错误 1451：1451：无法删除或更新父行：外键约束失败
Now, what confuses me before was that, no other table entries have been using the entry that i am deleting on 'product' table. This delete should go smoothly.
现在，之前让我感到困惑的是，没有其他表条目一直在使用我在“产品”表上删除的条目。这次删除应该很顺利。
So, i tried removing my TRIGGER on the 'product' table and BLAM, the delete was a success.
This means the error lies on my TRIGGER, can you help me point out where exactly (if not on the trigger) and WHY the error happened.
因此，我尝试删除“产品”表和 BLAM 上的 TRIGGER，删除成功。
这意味着错误出在我的触发器上，您能帮我指出错误发生的确切位置（如果不在触发器上）以及错误发生的原因。
采纳答案by wolfgangwalther
Before deleting the product your trigger will insert into product_audit. If the product_idcolumn in product_auditis a foreign key to product, then you can't delete this row from productanymore, because it is a parent to the newly created row in product_audit.
在删除产品之前，您的触发器将插入到product_audit. 如果product_idin 列product_audit是 to 的外键product，则您不能再删除该行product，因为它是 in 中新创建的行的父级product_audit。
Try to remove the foreign key constraint from product_audit.
尝试从product_audit.
Since you didn't show the table definition for product_audit, the above is guessing in that regard. (At the time of writing, that is. But my guess was correct!)
由于您没有显示 的表定义product_audit，因此以上是在这方面的猜测。（在撰写本文时，就是这样。但我的猜测是正确的！）
回答by Payam
The entry which you're trying to deletein your producttable is a parentto some other table. Meaning that, if you try to delete an entry from your producttable where product_id=1you have to make sure that all the entries refrencing to this entry should be deleted first.
您尝试delete在product表中输入的条目是parent其他表的条目。这意味着，如果您尝试从product表中删除一个条目，product_id=1您必须确保首先删除与该条目相关的所有条目。
Let's assume you have a table called user
假设您有一个名为的表 user
+--------+-------+--------+
| UserID | Name | Gender |
+--------+-------+--------+
| 1 | Jason | Male |
+--------+-------+--------+
| 2 | Sara | Female |
+--------+-------+--------+
| 3 | John | Male |
+--------+-------+--------+
and you have a table user_addresswhich is referencing to usertable with user_idbeing foreign key.
并且您有一个表user_address，该user表user_id以外键引用表。
+-----------+-----------+--------+
| AddressID | Address | UserID |
+-----------+-----------+--------+
| 1 | Address A | 1 |
+-----------+-----------+--------+
| 2 | Address B | 1 |
+-----------+-----------+--------+
| 3 | Address C | 2 |
+-----------+-----------+--------+
Now if you want to run a delete query on userlike this:
现在，如果您想像这样运行删除查询user：
delete from user where userID=1;
You have to make sure to delete all the children (dependencies) to it, which in this game is Address Aand Address B.
你必须确保删除它的所有子项（依赖项），在这个游戏中是Address A和Address B。
回答by Rafiqul Islam
You can check if product_idis used as FOREIGN KEYin any other table by running the follwing query.
您可以检查是否product_id被用作FOREIGN KEY通过运行follwing查询在其他任何表。
SELECT constraint_name, table_name
FROM information_schema.table_constraints
WHERE constraint_type = 'FOREIGN KEY'
AND table_schema = DATABASE()
AND constraint_name LIKE '%product_id%'
ORDER BY constraint_name;`
