MySQL multi delete row in 2 tables that MIGHT reference each other

Question

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)
...

Answer 1

Your problem is quite similar to this one and the same solution applies: first remove the references by setting to NULL the referencing columns in those rows that you intend to delete. Then delete.