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

score 379 · Answer 1 · edited Jul 24 '13 at 08:11

379

The simple way would be to disable the foreign key check; make the changes then re-enable foreign key check.

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

edited Jul 24 '13 at 08:11

michaelb958--GoFundMonica

4,617
7
31
35

answered Jul 24 '13 at 07:49

Alino Manzi

4,231
2
12
2

30

In my case: I just ran a large SQL file and one of the final statements failed, so I just wanna delete all tables, fix the syntax error, and rerun, making this exactly what I was looking for. – ekerner Nov 05 '14 at 17:18
1

If you were going to do this, why not just remove all of the constraints? – Sablefoste Jun 07 '16 at 12:23
2

It is useful when doing something like: `REPLACE INTO tab_with_constraint ...` – Maciej Łoziński Dec 28 '16 at 20:29
17

The only reason to upvote this answer is if you just want your code to stop yelling at you and forge deeper into spaghetti without understanding the code you are writing. The reason to have foreign keys in the first place is to enforce referential integrity. If you need to disable them to make your code shutup, you probably want to rethink your foreign keys, instead of disabling them. – cytinus Apr 25 '17 at 08:02
Note: Even if you disable `foreign key check` this will be only applicable for the `current active session` and once you close the session your db goes back to previous state. – Ashwin Mar 12 '19 at 13:10
In my case I have to replace one of my database name connected to other database so this is really good solution to me. – ßiansor Å. Ålmerol Jul 07 '20 at 01:39
sqlPro was "p-ing" me off until I saw this. Thank you!! – Sylar Oct 08 '20 at 11:47
2

@MaciekŁoziński Can you explain it why it is useful. You should only disable the checks if you know what you' re doing e.g. Import a sql backup otherwise I don't see a reason. And instead of a REPLACE INTO query you should check if the primary key is set and therefore > 0 then you execute a UPDATE query otherwise a INSERT query. Doing so you won't get a problem with the foreign key check. – Alexander Behling Dec 06 '21 at 14:19
this is really dangerous and really not the best practice, you can destroy your db if you remove constraints, do any action in the table and then try to add them later.. they exist for a reason, and unless you know exactly what you're doing - you should not remove them – Tzachi Elrom Jan 22 '23 at 14:58

score 152 · Accepted Answer · edited May 09 '17 at 13:49

152

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.

edited May 09 '17 at 13:49

Pherrymason

7,835
8
39
57

answered Dec 15 '09 at 06:15

OMG Ponies

325,700
82
523
502

4

In the first line: don't you think it should be "that it references" instead of "that references it"? Or have I misunderstood how the references terminology is supposed to work? – Abraham Philip Jul 26 '15 at 17:31
8

@AbrahamPhilip I was thinking the same thing. advertisers references jobs. – keyser Aug 20 '15 at 07:01

Asaph · Answer 3 · 2015-11-13T22:55:22.367

Under your current (possibly flawed) design, you must delete the row out of the advertisers table before you can delete the row in the jobs table that it references.

Alternatively, you could set up your foreign key such that a delete in the parent table causes rows in child tables to be deleted automatically. This is called a cascading delete. It looks something like this:

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

Having said that, as others have already pointed out, your foreign key feels like it should go the other way around since the advertisers table really contains the primary key and the jobs table contains the foreign key. I would rewrite it like this:

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

And the cascading delete won't be necessary.

score 37 · Answer 4 · answered Aug 20 '20 at 17:12

37

Disable the foreign key check and make the changes then re-enable foreign key check.

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

answered Aug 20 '20 at 17:12

Alan

9,167
4
52
70

score 21 · Answer 5 · answered Nov 04 '19 at 12:20

I tried the solution mentioned by @Alino Manzi but it didn't work for me on the WordPress related tables using wpdb.

then I modified the code as below and it worked

SET FOREIGN_KEY_CHECKS=OFF; //disabling foreign key

//run the queries which are giving foreign key errors

SET FOREIGN_KEY_CHECKS=ON; // enabling foreign key

score 19 · Answer 6 · answered Nov 11 '16 at 10:06

19

If you want to drop a table you should execute the following query in a single step

SET FOREIGN_KEY_CHECKS=0; DROP TABLE table_name;

answered Nov 11 '16 at 10:06

Abin John

381
2
7

score 7 · Answer 7 · answered Dec 15 '09 at 06:16

7

I think that your foreign key is backwards. Try:

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

answered Dec 15 '09 at 06:16

Tom H

46,766
14
87
128

score 5 · Answer 8 · edited Oct 10 '19 at 14:56

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 the end of your foreign key:

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

Check out Foreign Key constraints

score 4 · Answer 9 · answered May 14 '14 at 07:30

4

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

answered May 14 '14 at 07:30

Ran Adler

3,587
30
27

Marius Cucuruz · Answer 10 · 2017-05-09T10:54:41.930

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.

score 2 · Answer 11 · answered May 08 '13 at 07:25

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!

score 2 · Answer 12 · answered Sep 01 '17 at 16:17

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

score 1 · Answer 13 · answered Jul 26 '16 at 09:50

if you need to support client as soon as possible, and do not have access to

FOREIGN_KEY_CHECKS

so that data integrity can be disabled:

1) delete foreign key

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

2) activate your deleting operation thruogh sql or api

3) add the foreign key back to schema

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

however, it is a hot-fix, so it is on your own risk, because the main flaw of such approach is that it is needed afterwards to keep the data integrity manually.

score 1 · Answer 14 · answered Jan 06 '18 at 20:01

You could create a trigger to delete the referenced rows in before deleting the job.

    DELIMITER $$
    CREATE TRIGGER before_jobs_delete 
        BEFORE DELETE ON jobs
        FOR EACH ROW 
    BEGIN
        delete from advertisers where advertiser_id=OLD.advertiser_id;
    END$$
    DELIMITER ;

score 1 · Answer 15 · answered Jul 11 '19 at 04:18

The main problem with this erorr Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails is that it doesn't let you know which table contains the FK failure, so it is difficult to solve the conflict.

If you use MySQL or similar, I found out that you can create an ER diagram for your database, then you can review and safely remove any conflicts triggering the error.

Use MySQL workbench
Click on Database -> Reverse Engineering
Select a correct connection
Next till the end, remember to select database & tables that need examine
Now you have the ER diagram, you can see which table have FK conflict

Kipruto · Answer 16 · 2023-01-10T16:31:10.707

1

Go to phpmyadmin copy your SQL query and paste into the insert query box.Uncheck the enable foreign key check before pressing go. When dropping you can Uncheck the box and proceed to drop the table. Should work

edited Jan 10 '23 at 16:31

answered Jan 10 '23 at 16:12

Kipruto

721
6
16

One should never do that; otherwise simply don't add a FK constraint. – belka Aug 30 '23 at 07:53

GreenCat · Answer 17 · 2022-01-12T21:17:43.390

0

This error can still when working in Symfony with Doctrine Query Language, i added onDelete in Entity file

/**
 * @ORM\ManyToOne(targetEntity=Pricelist::class)
 * @ORM\JoinColumn(name="pricelist_id", referencedColumnName="id", onDelete="SET NULL")
 */

edited Jan 12 '22 at 21:17

answered Jan 12 '22 at 20:15

GreenCat

59
1
5

1

As it’s currently written, your answer is unclear. Please [edit] to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 12 '22 at 20:52
Your answer could be improved with additional supporting information. Please [edit] to add further details, such as citations or documentation, so that others can confirm that your answer is correct. You can find more information on how to write good answers [in the help center](/help/how-to-answer). – Community Jan 13 '22 at 04:18

score -1 · Answer 18 · answered Dec 15 '09 at 06:16

-1

Maybe you should try ON DELETE CASCADE

answered Dec 15 '09 at 06:16

45

Blindly adding a cascading delete (which will destroy data) without understanding the problem is just about the worst thing that one could do. – Tom H Dec 15 '09 at 06:18

score -2 · Answer 19 · edited Aug 06 '21 at 07:43

-2

This happened to me as well and due to a dependency and reference from other tables, I could not remove the entry. What I did is, I added a delete column (of type boolean) to the table. The value in that field showed whether the item is marked for deletion or not. If marked for deletion, then it would not be fetched or used, otherwise, it would be used.

edited Aug 06 '21 at 07:43

David Kariuki

1,522
1
15
30

answered May 10 '20 at 23:59

Tariq Kamal

468
1
8
17

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

19 Answers19

Linked

Related