Cannot delete or update a parent row: a foreign key constraint fails

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

The simple way to disable the foreign key check is to make the changes then re-enable foreign key check like this:

SET FOREIGN_KEY_CHECKS=0; -- to disable them

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