Cannot delete or update a parent row: a foreign ke

Question

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

Answer 1

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.

Answer 2

You need to delete it by order There are dependency in the tables

Answer 3

I had this problem in laravel migration too
the order of drop tables in down() method does matter

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

may not work, but if you change the order, it works.

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

Answer 4

How about this alternative I've been using: allow the foreign key to be NULL and then choose 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.

Hope this helps.

Answer 5

If there are more than one job having the same advertiser_id, then your foreign key should be:

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 your foreign key

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

Check out Foreign Key constraints

Answer 6

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!