主页 / dba / 问题 / 236911
Accepted
Dinesh Kumar
Asked: 2019-04-29 23:38:29 +0800 CST

Mysql 中“ON-DELETE-CASCADE”和“ON-DELETE-RESTRICT”约束混合的标准行为

  • 772

在mysql 5.6中，考虑这两个在 A、B、C 和 D 之间创建关系的示例。

示例 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;

结果是所有行都被删除。

示例 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;

请注意，唯一的区别是d的外键是RESTRICT的变化。然而，这个例子失败了

错误代码：1451 无法删除或更新父行：外键约束失败（hello. d，CONSTRAINT d_ibfk_1FOREIGN KEY（id）REFERENCES b（id））

从逻辑上讲，它与示例 1相同。在没有查看MySQL源代码的情况下，我强烈怀疑外键是根据其名称按词法顺序“应用”的。这种情况下的标准行为 (ANSI-SQL) 是什么？

mysql foreign-key

1 个回答

  1. Best Answer

    我修改了示例 1，以便我尝试过的所有供应商都接受语法。事实证明，唯一拒绝该场景的测试 DBMS 是 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

    请注意，对于 Oracle 和 SQLServer，必须稍微修改外键。查看Dinesh Kumar提供的链接

    甲骨文 数据库

    Db2 抛出如下异常：

    SQL20255N FOREIGN KEY .. 无效，因为它会导致后代表...通过具有冲突删除规则的多个关系删除连接到其祖先表...。冲突是在后代表上约束...和...的删除规则之间。原因代码 =“3”。SQLSTATE=42915 SQLCODE=-20255

    我浏览了 7IWD2-02-Foundation-2011-12.pdf，可以从以下网址下载：

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

    但我没有发现任何与此相关的内容。

    在我看来，Db2 在这方面的表现似乎很正常，但这只是我的看法。

    • 1

相关问题