不能删除或更新父行:外键约束失败

当进行:

DELETE FROM `jobs` WHERE `job_id` =1 LIMIT 1

错误:

#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))

这是我的表格:

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`);

当前回答

如果您需要尽快支持客户端，又没有权限访问

FOREIGN_KEY_CHECKS

因此，数据完整性可以被禁用:

1)删除外键

ALTER TABLE `advertisers` 
DROP FOREIGN KEY `advertisers_ibfk_1`;

2)通过SQL或API激活您的删除操作

3)将外键添加回schema

ALTER TABLE `advertisers`
  ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `jobs` (`advertiser_id`);

然而，这是一个热修复，因此风险由您自己承担，因为这种方法的主要缺陷是事后需要手动保持数据完整性。

2016-07-26 09:50:57

其他回答

我觉得你的外键放反了。试一试:

ALTER TABLE 'jobs'
ADD CONSTRAINT `advertisers_ibfk_1` FOREIGN KEY (`advertiser_id`) REFERENCES `advertisers` (`advertiser_id`)

2009-12-15 06:16:09

我在幼虫迁徙中也遇到过这个问题在down()方法中，下拉表的顺序很重要

Schema::dropIfExists('groups');
Schema::dropIfExists('contact');

也许不行，但如果你改变顺序，就行了。

Schema::dropIfExists('contact');
Schema::dropIfExists('groups');

2017-09-01 16:17:20

简单的方法是禁用外键检查;进行更改，然后重新启用外键检查。

SET FOREIGN_KEY_CHECKS=0; -- to disable them
SET FOREIGN_KEY_CHECKS=1; -- to re-enable them

2013-07-24 07:49:05

也许你应该试试ON DELETE CASCADE

2009-12-15 06:16:28

禁用外键检查并进行更改，然后重新启用外键检查。

SET FOREIGN_KEY_CHECKS=0; -- to disable them
DELETE FROM `jobs` WHERE `job_id` = 1 LIMIT 1 
SET FOREIGN_KEY_CHECKS=1; -- to re-enable them

2020-08-20 17:12:19

不能删除或更新父行:外键约束失败

推荐文章

最新文章

标签