Home / Questions / 105890
Accepted
Angel Zambrano
Asked: 2020-09-29 14:26:14 +0800 CST 2020-09-29 14:26:14 +0800 CST

MySQL 中的 CASCADE、SET NULL、RESTRICT、NO ACTION 是什么意思？

  • 772

我一直在使用关系表，但事实证明，当子行被删除或更新时，有 4 种类型的“动作”，我不知道这可能意味着什么，尽管我将 CASCADE 放在所有这些表上了解它们的功能。

他们帮助我？

在此处输入图像描述

mysql

3 Answers

  1. Best Answer

    它们被称为参照完整性约束。

    使用 InnoDB 存储引擎的 MySQL 数据库的不同表之间的关系可以通过外键约束（“Foreign Key Constraints”）的形式来指定，这样数据库本身就可以防止操作被执行，从而导致不一致。

    外键约束的默认行为是防止由于语句而对数据库进行更改，DELETE或者UPDATE，如果这会导致引用完整性失败。

    我们将首先总结不同的参照完整性约束，利用一些图像来处理ON DELETE.

    这些图像考虑了两个表personas并按ciudades列相关ciudad_id：

    餐桌设计

    图片由 A. Cedano 为 stackoverflow.es 提供

    RESTRICT

    • RESTRICT：这是默认行为，可防止违反参照完整性的修改。

      在图像中，我们看到了这个查询的结果：

      DELETE FROM ciudades WHERE ciudad_id=4;

      我们看到该记录可以被删除，因为表中没有相关记录personas。

    严格

    图片由 A. Cedano 为 stackoverflow.es 提供

    而是这个查询：

        DELETE FROM ciudades WHERE ciudad_id=1;

    它会抛出一条错误消息：

    无法删除或更新父行：外键约束失败 ( db. personas, CONSTRAINT personas_ibfk_1FOREIGN KEY ( ciudad_id) REFERENCES ciudades( ciudad_id))

    因为DELETE他违反了限制。如果行1ofciudades被删除，行1和4ofpersonas将是孤立的，即在 table 中没有关系ciudades。

    CASCADE

    • CASCADE: 当父表的记录被删除时（在语句中DELETE）删除从属表的记录，或者在被引用键的值被更新时（在语句中UPDATE）更新子键的值。

      在图像中，我们看到了这个查询的结果：

      DELETE FROM ciudades WHERE ciudad_id=1;

      CASCADE 在这里，所有等于 的记录personas都将在 cascade 中删除，很明显，它将在id 的城市中删除。ciudad_id1ciudades1

    删除级联

    图片由 A. Cedano 为 stackoverflow.es 提供

    SET NULL

    • SET NULL:NULL当主表中的记录被删除或被引用字段的值被修改时，设置辅助键的值。

      我们在图像中看到的是这个查询的结果：

      DELETE FROM ciudades WHERE ciudad_id=1;

      ciudad_id此处表格列personas将值设置为NULL，在所有ciudad_id等于 的行中1。很明显，它将在ciudadesid 的城市中被删除1。

    删除设置为空

    图片由 A. Cedano 为 stackoverflow.es 提供

    对于在这种情况下发生的情况，重要的是要注意CREATE TABLE：

    CREATE TABLE IF NOT EXISTS personas 
(
    persona_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 
    persona_nom VARCHAR(70),
    ciudad_id INT NOT NULL,
    FOREIGN KEY fk_ciudad(ciudad_id) REFERENCES ciudades(ciudad_id)
    ON DELETE SET NULL

)ENGINE=INNODB;

    它会抛出错误：

    无法添加外键约束

    我认为不难找出原因:)

    NO ACTION

    • NO ACTION: 在 MySQL 中它的工作方式与RESTRICT. 请参阅下面的说明。

    测试代码

    我在这里留下一个演示，以便我们可以用真实数据测试每个限制是如何工作的。我们只需要改变限制的配置来CREATE TABLE放置不同的可能性。

    该示例基于，但如果我们愿意DELETE，我们可以编写和测试查询。UPDATE

    这是链接。

    VER DEMO

    有关限制的更多信息

    以下内容几乎全部取自 MySQL 文档。

    创建表时通常会指示约束，这两个主外键（CREATE TABLE）。那些与参照完整性相关的必须明确指出，因为 MySQL 不会根据指定的主外键推断它们。如果在此期间未创建CREATE TABLE它们，则可以稍后使用ALTER TABLE. 该文档解释了如何执行此操作。

    有两种类型的约束：ON DELETE和ON UPDATE。并且在它们中依次有几种可能性（两者都是相同的）。

    让我们用文档来解释它：

    对于支持外键的存储引擎，如果父表中没有匹配的候选键值，MySQL 会拒绝任何操作INSERT或UPDATE尝试在子表上创建外键值。

    当UPDATEor操作DELETE影响父表中具有匹配行的子表中的键值时，结果取决于使用ON UPDATEandON DELETE子句子句指定的引用操作FOREIGN KEY。MySQL 支持关于要采取的操作的五个选项：

    • CASCADE：删除或更新父表行，并自动删除或更新子表中匹配的行。只要ON DELETE CASCADE它们ON UPDATE CASCADE兼容。在两个表之间，未定义ON UPDATE CASCADE作用于父表或子表中同一列的多个子句。

    注意 级联外键操作不会触发触发器。

    • SET NULL：删除或更新父表行并将子表中的外键列设置为NULL。ON DELETE SET NULL支持and子句ON UPDATE SET NULL。

    如果您指定一个 action SET NULL，请确保您没有将子表的列声明为NOT NULL。

    • RESTRICT：拒绝对父表的删除或更新操作。指定RESTRICT(or ) 与省略orNO ACTION子句相同。ON DELETEON UPDATE

    • NO ACTION: 一个标准的 SQL 关键字。在 MySQL 中，相当于RESTRICT. 如果引用的表中存在相关的外键值，MySQL 服务器将拒绝对父表的删除或更新操作。一些数据库系统有延迟检查，NO ACTION这是一种延迟检查。在 MySQL 中，外键约束是立即检查的，所以它NO ACTION与RESTRICT.

    • SET DEFAULT：这个动作被 MySQL 解析器识别，但是 InnoDB 和 NDB 都拒绝包含ON DELETE SET DEFAULTor子句的表定义ON UPDATE SET DEFAULT。

    对于未指定的ON DELETE或ON UPDATE，默认操作始终为RESTRICT。

    MySQL 支持表中一列和另一列之间的外键引用。（列不能有对其自身的外键引用。）在这些情况下，“子表记录”实际上是指同一个表中的依赖记录。

    存储生成列上的外键约束不能使用ON UPDATE CASCADE、ON DELETE SET NULL、ON UPDATE SET NULL或。ON DELETE SET DEFAULTON UPDATE SET DEFAULT

    外键约束不能引用虚拟生成的列。

    对于与外键和生成列相关的 InnoDB 限制，请参阅第 14.8.1.6 节，“InnoDB 和 FOREIGN KEY 限制”。

    • 68

  2. 引用操作指示对父表的操作将如何UPDATE影响DELETE子表中的相关行。您可以在MySQL 文档中找到操作之间的差异：

    • CASCADE：当你在父表上做UPDATE/DELETE时，它会自动更新/删除子表中的相关行。
    • SET NULL: 对父表做UPDATE/DELETE时，子表相关行的外键会设置为NULL. 确保这些字段不受限制，NOT NULL否则您可能会收到错误，这一点很重要。
    • RESTRICT：如果您尝试在父表上执行UPDATE/的操作，DELETE它将被自动拒绝。
    • NO ACTION: 这是一个 SQL 关键字，在 MySQL 中相当于RESTRICT.

    第五个值SET DEFAULT虽然被 MySQL 处理器接受，但可能会导致 InnoDB 和 NDB 出现问题，这将拒绝使用该值定义的表。

    • 13

  3. 在 MySQL 中，目前只有 InnoDB 引擎支持外键约束。InnoDB 中外键约束的语法如下：

    
ALTER TABLE tbl_name
    ADD [CONSTRAINT [symbol]] FOREIGN KEY
    [index_NAME] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name, ...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

    级联：

    更新/删除父表记录时，同时更新/删除子表匹配记录

    设置为空：

    更新/删除父表中的记录时，将子表中匹配记录的列设置为null【注意子表中对应的外键列不能设置为NOT NULL】

    不采取行动：

    拒绝更新或删除或删除父表

    严格 ：

    拒绝更新或删除父表

    注意：指定 RESTRICT 或 NOACTION 与忽略 ON DELETE 或 ON UPDATE 具有相同的效果。

    • 0