Cannot delete or update a parent row: a foreign key constraint fails
MysqlSqlMysql Problem Overview
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`);
Mysql Solutions
Solution 1 - Mysql
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
Solution 2 - Mysql
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.
Solution 3 - Mysql
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.
Solution 4 - Mysql
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
Solution 5 - Mysql
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;
Solution 6 - Mysql
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
Solution 7 - Mysql
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`)
Solution 8 - Mysql
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
Solution 9 - Mysql
You need to delete it by order There are dependency in the tables
Solution 10 - Mysql
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!
Solution 11 - Mysql
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.
Solution 12 - Mysql
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');
Solution 13 - Mysql
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:
-
delete foreign key
ALTER TABLE
advertisers
DROP FOREIGN KEYadvertisers_ibfk_1
; -
activate your deleting operation thruogh sql or api
-
add the foreign key back to schema
ALTER TABLE
advertisers
ADD CONSTRAINTadvertisers_ibfk_1
FOREIGN KEY (advertiser_id
) REFERENCESjobs
(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.
Solution 14 - Mysql
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 ;
Solution 15 - Mysql
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
Solution 16 - Mysql
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")
*/
Solution 17 - Mysql
Maybe you should try ON DELETE CASCADE
Solution 18 - Mysql
Basically, The reason behind these type of error is eventually you are trying to delete a tupple which has primary key (root table) & that primary key is used in child table as a foreign key. In this scenario in order to delete parent table data you have to remove child table data (in which foreign key is used). Thanks
Solution 19 - Mysql
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.