MySQL Multi DELETE If relationship exists with foriegn key

Question

I'm trying to (in a single statement) delete a row and all its relationships, even if all those relationships don't exist. Cascade on delete is not on option, and I would prefer to avoid subqueries.

Here is an example of what fails due to foreign key relationships:

CREATE TABLE test(id integer, title varchar(100), primary key(id));
INSERT into test(id, title) values(1, "Hello");
CREATE TABLE ref_a(id integer, test_id integer, primary key(id), key(test_id), constraint foreign key(test_id) references test(id));
INSERT into ref_a(id, test_id) values(1, 1);
CREATE TABLE ref_b(id integer, test_id integer, primary key(id), key(test_id), constraint foreign key(test_id) references test(id));
SET GLOBAL FOREIGN_KEY_CHECKS=1;


DELETE test, ref_a, ref_b FROM test
LEFT JOIN ref_a ON ref_a.test_id = test.id
LEFT JOIN ref_b ON ref_b.test_id = test.id
WHERE test.id = 1;

This fails with the error

Error Code: 1451. Cannot delete or update a parent row: a foreign key constraint fails (`product`.`ref_a`, CONSTRAINT `ref_a_ibfk_1` FOREIGN KEY (`test_id`) REFERENCES `test` (`id`))

Is this possible to do?

DB is InnoDb. MySql v 5.6.36

Answer 1

For your issue there are three options:

1. Enable ON DELETE CASCADE. But that is not an option in your case apparently

2. Disable foreign_key_checks before running your query, and re-enable it afterwards

3. Run two queries; first deleting referencing rows (ref_a, ref_b), then the rows in test

Otherwise you this will not be possible, that's what foreign keys are for; to ensure data consistency.