I have got 2 MySQL tables that have (InnoDB) foreign keys going into each other. For example,
-- Adminer 4.2.3 MySQL dump
SET NAMES utf8;
SET time_zone = '+00:00';
SET foreign_key_checks = 0;
SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO';
DROP TABLE IF EXISTS `a`;
CREATE TABLE `a` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`null_or_b_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `null_or_b_id` (`null_or_b_id`),
CONSTRAINT `a_ibfk_1` FOREIGN KEY (`null_or_b_id`) REFERENCES `b` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `a` (`id`, `null_or_b_id`) VALUES
(1, NULL),
(2, 2),
(4, 3),
(3, 4),
(5, 5),
(6, 6),
(7, 7),
(8, 8);
DROP TABLE IF EXISTS `b`;
CREATE TABLE `b` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`null_or_a_id` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
KEY `null_or_a_id` (`null_or_a_id`),
CONSTRAINT `b_ibfk_1` FOREIGN KEY (`null_or_a_id`) REFERENCES `a` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `b` (`id`, `null_or_a_id`) VALUES
(1, NULL),
(8, NULL),
(2, 2),
(4, 3),
(3, 4),
(5, 6),
(6, 7),
(7, 8);
-- 2016-02-03 06:45:07
What I want to do is delete the records with the ids 1, 2, 3 and 5 from a and delete any records that need to deleted in both a and b due to the foreign key constraints. I have tried:
delete from a where a.id in (1,2,3,5);
delete a,b from a left join b on b.null_or_a_id = a.id where a.id in (1,2,3,5);
Both the above give the same error:
Error in query (1451): Cannot delete or update a parent row: a foreign key constraint fails (`test/multi_delete_with_references`.`b`, CONSTRAINT `b_ibfk_1` FOREIGN KEY (`null_or_a_id`) REFERENCES `a` (`id`))
I get the same error even if I remove the foreign key constraint on b defined in a.
Things I can't do:
- Disable foreign key checks: because both the tables are also referenced by other tables and I don't want those tables to have orphaned rows, if this delete is going to cause such orphaned rows I need this delete to fail.
- Delete from the child table first: because as you can see in the case of rows with id 2 in both tables, they reference each other so one can't be deleted without the other, also in the case of rows with ids 3 and 4 in both tables, they form a self-referencing chain.
I have looked at the answer here and it won't work for me because both the tables reference each other.
Is there a way out of this?
BTW, I also tried to generate complex, nested queries dynamically but it ended up being endless:
delete from a where id in (1,2,3,5);
delete from b where null_or_a_id is not null and null_or_a_id in (select * from (select id from a where id in (1,2,3,5)) x);
delete from a where null_or_b_id is not null and null_or_b_id in (select * from (select id from b where null_or_a_id is not null and null_or_a_id in (select * from (select id from a where id in (1,2,3,5)) x)) x);
delete from b where null_or_a_id is not null and null_or_a_id in (select * from (select id from a where null_or_b_id is not null and null_or_b_id in (select * from (select id from b where null_or_a_id is not null and null_or_a_id in (select * from (select id from a where id in (1,2,3,5)) x)) x)) x)
...
