MySQL 无法删除或更新父行：外键约束失败
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1905470/
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
Cannot delete or update a parent row: a foreign key constraint fails
提问by steven
When doing:
做的时候：
DELETE FROM `jobs` WHERE `job_id` =1 LIMIT 1
It errors:
它错误：
#1451 - Cannot delete or update a parent row: a foreign key constraint fails
(paymesomething.advertisers, CONSTRAINT advertisers_ibfk_1 FOREIGN KEY
(advertiser_id) REFERENCES jobs (advertiser_id))
Here are my tables:
这是我的表：
CREATE TABLE IF NOT EXISTS `advertisers` (
`advertiser_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(255) NOT NULL,
`password` char(32) NOT NULL,
`email` varchar(128) NOT NULL,
`address` varchar(255) NOT NULL,
`phone` varchar(255) NOT NULL,
`fax` varchar(255) NOT NULL,
`session_token` char(30) NOT NULL,
PRIMARY KEY (`advertiser_id`),
UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
INSERT INTO `advertisers` (`advertiser_id`, `name`, `password`, `email`, `address`, `phone`, `fax`, `session_token`) VALUES
(1, 'TEST COMPANY', '', '', '', '', '', '');
CREATE TABLE IF NOT EXISTS `jobs` (
`job_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`advertiser_id` int(11) unsigned NOT NULL,
`name` varchar(255) NOT NULL,
`shortdesc` varchar(255) NOT NULL,
`longdesc` text NOT NULL,
`address` varchar(255) NOT NULL,
`time_added` int(11) NOT NULL,
`active` tinyint(1) NOT NULL,
`moderated` tinyint(1) NOT NULL,
PRIMARY KEY (`job_id`),
KEY `advertiser_id` (`advertiser_id`,`active`,`moderated`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=2 ;
INSERT INTO `jobs` (`job_id`, `advertiser_id`, `name`, `shortdesc`, `longdesc`, `address`, `active`, `moderated`) VALUES
(1, 1, 'TEST', 'TESTTEST', 'TESTTESTES', '', 0, 0);
ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`);
采纳答案by OMG Ponies
As is, you must delete the row out of the advertisers table before you can delete the row in the jobs table that it references. This:
照原样，您必须先从广告商表中删除该行，然后才能删除它引用的职位表中的行。这个：
ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`)
REFERENCES `jobs` (`advertiser_id`);
...is actually the opposite to what it should be. As it is, it means that you'd have to have a record in the jobs table before the advertisers. So you need to use:
...实际上与它应该是相反的。实际上，这意味着您必须在广告商之前在工作表中拥有记录。所以你需要使用：
ALTER TABLE `jobs`
ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`)
REFERENCES `advertisers` (`advertiser_id`);
Once you correct the foreign key relationship, your delete statement will work.
一旦您纠正了外键关系，您的删除语句就会起作用。
回答by Alino Manzi
The simple way would be to disable the foreign key check; make the changes then re-enable foreign key check.
简单的方法是禁用外键检查；进行更改然后重新启用外键检查。
SET FOREIGN_KEY_CHECKS=0; -- to disable them
SET FOREIGN_KEY_CHECKS=1; -- to re-enable them
回答by Asaph
Under your current (possibly flawed) design, you must delete the row out of the advertisers table beforeyou can delete the row in the jobs table that it references.
在您当前（可能有缺陷）的设计中，您必须先从广告商表中删除该行，然后才能删除它引用的职位表中的行。
Alternatively, you could set up your foreign key such that a delete in the parent table causes rows in child tables to be deleted automatically. This is called a cascading delete. It looks something like this:
或者，您可以设置外键，以便父表中的删除会导致子表中的行自动删除。这称为级联删除。它看起来像这样：
ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1`
FOREIGN KEY (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`)
ON DELETE CASCADE;
Having said that, as others have already pointed out, your foreign key feels like it should go the other way around since the advertisers table really contains the primary key and the jobs table contains the foreign key. I would rewrite it like this:
话虽如此，正如其他人已经指出的那样，您的外键感觉应该反过来，因为广告客户表确实包含主键，而工作表包含外键。我会像这样重写它：
ALTER TABLE `jobs`
ADD FOREIGN KEY (`advertiser_id`) REFERENCES `advertisers` (`advertiser_id`);
And the cascading delete won't be necessary.
并且不需要级联删除。
回答by Abin John
If you want to drop a table you should execute the following query in a single step
如果你想删除一个表，你应该在一个步骤中执行以下查询
SET FOREIGN_KEY_CHECKS=0; DROP TABLE table_name;
SET FOREIGN_KEY_CHECKS=0; 删除表表名；
回答by Moh .S
I tried the solution mentioned by @Alino Manzi but it didn't work for me on the WordPress related tables using wpdb.
我尝试了@Alino Manzi 提到的解决方案，但它在使用 wpdb 的 WordPress 相关表上对我不起作用。
then I modified the code as below and it worked
然后我修改了下面的代码并且它起作用了
SET FOREIGN_KEY_CHECKS=OFF; //disabling foreign key
//run the queries which are giving foreign key errors
SET FOREIGN_KEY_CHECKS=ON; // enabling foreign key
回答by Tom H
I think that your foreign key is backwards. Try:
我认为你的外键是倒退的。尝试：
ALTER TABLE 'jobs'
ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `advertisers` (`advertiser_id`)
回答by SO User
If there are more than one job having the same advertiser_id, then your foreign key should be:
如果有多个工作具有相同的广告商 ID，那么您的外键应该是：
ALTER TABLE `jobs`
ADD CONSTRAINT `advertisers_ibfk_1`
FOREIGN KEY (`advertiser_id`)
REFERENCES `advertisers` (`advertiser_id`);
Otherwise (if its the other way round in your case), if you want the rows in advertiser to be automatically deleted if the row in job is deleted add the 'ON DELETE CASCADE' option to the end of your foreign key:
否则（如果您的情况相反），如果您希望在删除作业中的行时自动删除广告商中的行，请将“ON DELETE CASCADE”选项添加到外键的末尾：
ALTER TABLE `advertisers`
ADD CONSTRAINT `advertisers_ibfk_1`
FOREIGN KEY (`advertiser_id`)
REFERENCES `jobs` (`advertiser_id`)
ON DELETE CASCADE;
Check out Foreign Key constraints
查看外键约束
回答by Ran Adler
You need to delete it by order There are dependency in the tables
需要按顺序删除表中有依赖
回答by Quy Le
When you create database or create tables
创建数据库或创建表时
You should add that line at top script create database or table
您应该在顶部脚本创建数据库或表中添加该行
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1;
Now you want to delete records from table? then you write as
现在要从表中删除记录？然后你写成
SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=1;
DELETE FROM `jobs` WHERE `job_id` =1 LIMIT 1
Good luck!
祝你好运！
回答by Marius Cucuruz
How about this alternative I've been using: allow the foreign key to be NULLand then choose ON DELETE SET NULL.
我一直在使用的这个替代方法如何：允许外键为NULL然后选择ON DELETE SET NULL。
Personally I prefer using both "ON UPDATE CASCADE" as well as "ON DELETE SET NULL" to avoid unnecessary complications, but on your set up you may want a different approach. Also, NULL'ing foreign key values may latter lead complications as you won't know what exactly happened there. So this change should be in close relation to how your application code works.
我个人更喜欢同时使用“ ON UPDATE CASCADE”和“ ON DELETE SET NULL”以避免不必要的并发症，但在您的设置中，您可能需要不同的方法。此外，将外键值设为 NULL 后可能会导致并发症，因为您不知道那里究竟发生了什么。因此，此更改应与您的应用程序代码的工作方式密切相关。
Hope this helps.
希望这可以帮助。
