Home/ Questions/How to avoid the error "cannot delete or update a parent row: a foreign key constraint fails"?
Next
Answered
Léa Hamel
  • 7
Asked: 2022-05-17T12:40:05+00:00 In:

How to avoid the error “cannot delete or update a parent row: a foreign key constraint fails”?

  • 7

. Advertisement .

..3..

. Advertisement .

..4..

How to solve the problem – cannot delete or update a parent row: a foreign key constraint fails? I have the sample detail:

DELETE FROM `jobs` WHERE `job_id` =1 LIMIT 1
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`);

While I was running it, I found the warning message:

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

That is my question in my midterm exam, and it is urgent. I searched the solutions on some websites, but I didn’t get it. I may miss any line or other changes. I appreciate your assistance!

a foreign key constraint fails
Share

2 Answers

  1. Best Answer
    Expert

    The cause: I think the cause is that you must delete the advertisers table row before you can delete the jobs table row that it refers to. This:

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

    is the exact opposite of what should be done. As is, you’d have to have a record in the jobs table before the advertisers.

    The solution: You need to do as the following:

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

    Your delete statement will operate after you fix the foreign key relationship.

    • 11

  2. It is simple to disable foreign key checks and then make the necessary changes. 

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

Leave an answer

You must login to add an answer.

Need An Account,