1

Not sure if this is a generic question, but unless not, this is MySQL 5.6.

We have a database consisting of ~300 tables, INNODB, all connected with proper foreign keys. One of these tables can be seen as the parent. My desired end result is that I can execute one delete on this table, and have all related information in all other 300 tables deleted as well.

However, the declared foreign keys are a mix of RESTRICT and CASCADE for ON DELETE and I'm trying to understand the rule set when these are applied in recursion.

I've been trying to find information on this, but no luck. Testing appears to indicate that RESTRICT blocks a CASCADE, but not necessarily. Consider this example:

Tables: A, B, C
B -> A (ON DELETE CASCADE)
C -> A (ON DELETE CASCADE)
C -> B (ON DELETE RESTRICT)

In this example, rows from B and C are deleted when A is deleted. However, since C -> B has a RESTRICT one could think that matching rows would block a CASCADE. But this is not the case. However, if I add an additional table D like this:

Tables: A, B, C, D
B -> A (ON DELETE CASCADE)
C -> A (ON DELETE CASCADE)
C -> B (ON DELETE RESTRICT)
D -> C (ON DELETE RESTRICT)

A delete from A is blocked by the RESTRICT from D to C.

A simple approach would be to simply use CASCADE everywhere, but I'm curious about the rule set. Or if the behaviour is either implementation dependent or simply undefined.

Is it based on level of depth of the CASCADING? That is, the shortest path determines the effective ON DELETE rule to use? What if multiple paths have the same length, with different rules?

  • Are you sure the case with 3 tables (A, B, C) succeeds always? – ypercubeᵀᴹ Mar 2 '18 at 14:08
  • dbfiddle.uk 3 cases, some fail, some succeed – ypercubeᵀᴹ Mar 2 '18 at 14:24
  • By 3 cases, are you talking about the 3 possible cases you can have with rows in this example? My assumption here was was 3 tables will all non-nullable fields, with rows that make use of the foreign keys. – Matthias Mar 2 '18 at 14:42
  • I'm trying to understand the general rule set though, without regard to the rows. On a logical level. Ultimately, I want to implement an algorithm that by just analysing the tables' structure can verify whether a delete will always be a success, or not (only succeeding under some conditions is a fail). – Matthias Mar 2 '18 at 14:44
  • Yes, with 3 cases I meant the specific inserts. Deleting aid=1 and aid=2 fail. Deleting aid=3 succeeds. The desgin is exactly as your question, 3 tables, not nullable columns, and FKs from (B->A, C->B, C->A). – ypercubeᵀᴹ Mar 2 '18 at 14:52
1

I have managed to create a test case that at least for MySQL 5.6 demonstrates, for all intents and purposes, that the behaviour is undefined. Consider these 3 examples creating a "diamond"-shape between A, B, C and D.

Example 1

CREATE TABLE `a` (id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Delete from a is successful as expected, as all keys are CASCADE.

Example 2

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE CASCADE,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE RESTRICT
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Notice that d now has a RESTRICT to c. I would consider this case ambiguous as it forces you to consider which foreign key "wins". CASCADE or RESTICT, what is picked?

The result is that all rows are deleted.

Example 3

CREATE TABLE `a` (
    id INT UNSIGNED NOT NULL,
    PRIMARY KEY (id)
) ENGINE = INNODB;

CREATE TABLE `b` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `c` (
    id INT UNSIGNED NOT NULL,
    a INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (a) REFERENCES a (id) ON DELETE CASCADE
) ENGINE = INNODB;

CREATE TABLE `d` (
    id INT UNSIGNED NOT NULL,
    b INT UNSIGNED NOT NULL,
    c INT UNSIGNED NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (id) REFERENCES b (id) ON DELETE RESTRICT,
    FOREIGN KEY (id) REFERENCES c (id) ON DELETE CASCADE
) ENGINE = INNODB;

INSERT INTO a VALUES (1);
INSERT INTO b VALUES (1, 1);
INSERT INTO c VALUES (1, 1);
INSERT INTO d VALUES (1, 1, 1);

DELETE FROM a;

Notice that the only difference is the change of what foreign key is RESTRICT from d. This example however, fails with

Error Code: 1451 Cannot delete or update a parent row: a foreign key constraint fails (hello.d, CONSTRAINT d_ibfk_1 FOREIGN KEY (id) REFERENCES b (id))

While logically, it's the same as Example 2. Without having looked at the source code of MySQL, I strongly suspect that the foreign keys are "applied" in lexical order based on their name. This is also the order the foreign keys are listed by mysqldump.

Without knowing whether what I'm testing here has a defined behaviour or not based on a higher logic, it's hard to know if this demonstrates either a bug, or simply the behaviour in MySQL.

Regardless, as it stands, this example shows that in practical terms I'd say that for all intents and purposes, when having mixed CASCADE and RESTRICT, the behaviour is undefined as it's not in any way sound that you should depend on the names given to foreign keys.

  • 1
    Another possible explaination is that deletion is performed by something similar to a depth-first traversal that fails whenever a RESTRICT is encountered. Traversal itself could possibly make use of a lexical order. Would need to look at source code. Still, as close to undefined as it gets in my opinion. – Matthias Mar 5 '18 at 10:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.