MySQL multi delete row in 2 tables that CAN communicate with each other

Question

I have two MySQL tables that have InnoDB (foreign) keys coming together. 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 entries with identifiers 1, 2, 3 and 5 from a and delete all entries that need to be deleted in both a and b due to foreign key restrictions. I 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 above indicate 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 checking: because both tables also refer to other tables, and I do not want these tables to have orphan axes, if this deletion will result in such orphan rows I need this deletion to fail.
• : , id 2 , , , 3 4 , .

, , .

?

, , , :

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

: NULL , . .