0

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

Keozon
  • 998
  • 10
  • 25
  • _Is this possible to do?_ Did you try it? – RiggsFolly Jul 02 '19 at 14:27
  • It works quite nicely when I test it. – RiggsFolly Jul 02 '19 at 14:29
  • Did I try the method I posted above? Yes, I get an error about foreign key relationships, thus why I created this question. I tried a couple other things, but those require multiple statements, which I want to avoid. – Keozon Jul 02 '19 at 14:29
  • I get this ```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`))``` – Keozon Jul 02 '19 at 14:29
  • @Keozon based on your error, check this and other answers here: https://stackoverflow.com/questions/21659691/error-1452-cannot-add-or-update-a-child-row-a-foreign-key-constraint-fails/53099922#53099922 – Madhur Bhaiya Jul 02 '19 at 14:39

1 Answers1

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.

Renaud C.
  • 535
  • 2
  • 14
  • Thanks. Looks like it's not possible to do in one query, without ON DELETE CASCADE. I ended up just adding that. It just seems odd that it thinks data is inconsistent, as all references are being deleted in the query, so nothing would be invalid after the query is executed. – Keozon Jul 10 '19 at 13:50