Tables with mixed ON DELETE CASCADE and ON DELETE RESTRICT, rule set with recursion?

Question

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?

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