Mysql – Standard Behaviour for mix of ‘ON-DELETE-CASCADE’ and ‘ON-DELETE-RESTRICT’ constraints in Mysql

cascadedeleteforeign keyMySQLsql-standard

In mysql 5.6, Consider these 2 examples creating relationships 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 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;

The result is that all rows are deleted.

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 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 1. 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. What will be the standard behaviour (ANSI-SQL) in this scenario?

Best Answer

I modified example 1 so that the syntax is accepted by all vendors that I tried. It turns out that the only DBMS of the tested ones that reject the scenario is Db2 DB<>Fiddle:

MariaDB 10.2, 10.3 Yes
MySQL 5.6, 5.7, 8.0 Yes
Postgres 11 Yes

Oracle 11g release 2, 18 Yes
SQLServer 2017 Yes
Db2 V11 No

Note that the foreign keys have to be slightly modified for Oracle and SQLServer. See links provided by Dinesh Kumar

Oracle SQLServer

Db2 throws an exception like:

SQL20255N FOREIGN KEY .. is not valid because it would cause a descendent table ... to be delete-connected to its ancestor table ... through multiple relationships with conflicting delete rules. The conflict is between the delete rules of constraints ... and ... on the descendent table. Reason code = "3". SQLSTATE=42915 SQLCODE=-20255

I skimmed through 7IWD2-02-Foundation-2011-12.pdf which can be download from:

http://www.wiscorp.com/sql20nn.zip

but I did not find anything mentioned regarding this.

To me, it seems as if Db2 behave sanely in this regard, but that's just my opinion.

Related Question