4

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?

  • 1
    I strongly suspect that the foreign keys are "applied" in lexical order based on their name. Of course. The only alternative is random order, but it makes no sense at all. – Akina Apr 29 at 8:26
  • @Akina, can you elaborate this behaviour in Postgres? – Dinesh Kumar Apr 29 at 9:01
  • @Akina, 'lexical order based on their name' mean 'Iexical order of table names' , am I right? – Dinesh Kumar Apr 29 at 9:04
  • 1
    'lexical order based on their name' mean 'Iexical order of table names' , am I right? I cannot find any direct reference in user manual... except InnoDB and FOREIGN KEY Constraints: InnoDB performs cascading operations through a depth-first algorithm, based on records in the indexes corresponding to the foreign key constraints. This can explain (to self at least) why the first code succeeded whereas second one fails. Simply assume that the chained actions calculated for a table are calculated once. – Akina Apr 29 at 10:21
  • 1
    Sometimes FKs are more hassle then they are worth. – Rick James May 2 at 0:40
1

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.

  • seems like Oracle 11g link, and SQL Server [link] (dbfiddle.uk/…) is allowing this case, but db2 throws exception. link.. Please update your answer accordingly. – Dinesh Kumar May 10 at 14:43
  • Your link for Oracle misses ON DELETE RESTRICT in table d and your link for SQL server uses ON DELETE NO ACTION for the same table. The link for Db2 contains an error before what was discussed here. I'm not sure what I am supposed to update in my answer? – Lennart May 10 at 14:55
  • Oracle by default takes it to 'ON DELETE RESTRICT' ,if no action is specified, and SQL server uses 'ON DELETE NO ACTION' clause for restrict behaviour (I think there is no such clause 'ON DELETE RESTRICT' in SQL server). You have mentioned that Oracle, SqlServer won't allow this case, but they allow it actually – Dinesh Kumar May 10 at 15:00
  • I'll check it out later – Lennart May 10 at 15:28
  • 1
    @DineshKumar, updated answer, thanks for pointing that out – Lennart May 10 at 16:59

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.