无法删除或更新父行:外键约束失败

2021-11-20 00:00:00 sql mysql

做的时候:

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

推荐答案

照原样，您必须先从广告商表中删除该行，然后才能删除它引用的职位表中的行.这:

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.

相关文章