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
, CONSTRAINTd_ibfk_1
FOREIGN KEY (id
) REFERENCESb
(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?