主页 / dba / 问题 / 44956
Accepted
Johntron
Asked: 2013-06-21 06:18:32 +0800 CST

级联（ON DELETE/UPDATE）行为的良好解释

  • 772

我不是每天都设计模式，但是当我这样做时，我会尝试正确设置级联更新/删除以使管理更容易。我了解级联是如何工作的，但我永远不记得哪个表是哪个表。

例如，如果我有两个表 -Parent并且- 在该引用和 hasChild上有一个外键，哪些记录会触发级联，哪些记录会被级联删除？我的第一个猜测是删除记录时会删除记录，因为记录依赖于记录，但这是模棱两可的；它可能意味着删除记录时删除记录，也可能意味着删除记录时删除记录。那么它是哪一个？ChildParentON DELETE CASCADEChildParentChildParentON DELETEParentChildChildParent

我希望语法是ON PARENT DELETE, CASCADE，ON FOREIGN DELETE, CASCADE或类似的东西来消除歧义。有没有人有任何助记符来记住这一点？

mysql foreign-key

5 个回答

  1. Best Answer

    如果你喜欢ParentandChild术语并且你觉得它们很容易被记住，你可能会喜欢ON DELETE CASCADEto的翻译Leave No Orphans!

    这意味着当Parent删除（杀死）行时，表中不应存在孤立行Child。父行的所有子行也被杀死（删除）。如果这些孩子中的任何一个有孙子（通过另一个外键在另一个表中）并且有ON DELETE CASCADE定义，那么这些也应该被杀死（以及所有后代，只要定义了级联效应。）

    FOREIGN KEY约束本身也可以描述为（首先Allow No Orphans!）。Child如果没有Parent（父表中的一行），则不应在子表中允许（写入） No 。

    为了保持一致性，ON DELETE RESTRICT可以将其翻译为（不那么激进）You Can't Kill Parents!只有无子行可以被杀死（删除。）

    • 204

  2. 例如，如果我有两个表 - Parent 和 Child - 其中 Child 记录归 Parent 记录所有，那么哪个表需要 ON DELETE CASCADE？

    ON DELETE CASCADE 是外键声明中的可选子句。所以它与外键声明一起使用。（意思是，在“孩子”表中。）

    ...这可能意味着删除子记录时删除父记录，也可能意味着删除父记录时删除子记录。那么它是哪一个？

    解释外键声明的一种方法是，“该列的所有有效值都来自 'that_table' 中的 'that_column'。” 当您删除“子”表中的一行时，没有人关心。它不会影响数据完整性。

    当您从“父”表（从“that_table”）中删除一行时，您会从“子”表的可能值中删除一个有效值。为了保持数据完整性，您必须对“子”表做一些事情。级联删除是您可以做的一件事。

    章节和诗句，来自PostgreSQL 文档。

    限制和级联删除是两个最常见的选项。RESTRICT 防止删除引用的行。NO ACTION 表示如果在检查约束时仍然存在任何引用行，则会引发错误；如果您不指定任何内容，这是默认行为。（这两种选择之间的本质区别是 NO ACTION 允许将检查推迟到事务的后期，而 RESTRICT 不允许。） CASCADE 指定当一个引用的行被删除时，引用它的行应该被自动删除也是。还有两个其他选项：SET NULL 和 SET DEFAULT。当被引用的行被删除时，这些会导致引用行中的引用列被分别设置为空值或它们的默认值。请注意，这些并不能成为您遵守任何约束的借口。例如，如果一个操作指定了 SET DEFAULT 但默认值不满足外键约束，则操作将失败。

    • 46

  3. SQL:2011 规范

    有五个选项ON DELETE可以ON UPDATE适用于FOREIGN KEY. 这些被称为<referential actions>，直接来自 SQL:2011 规范

    • ON DELETE CASCADE：如果被引用表中的一行被删除，则引用表中所有匹配的行都被删除。
    • ON DELETE SET NULL：如果被引用表中的一行被删除，则引用表中所有匹配行中的所有引用列都被设置为null。
    • ON DELETE SET DEFAULT：如果被引用表的一行被删除，则引用表的所有匹配行中的所有引用列都被设置为该列的默认值。
    • ON DELETE RESTRICT：如果该行在引用表中有任何匹配的行，则禁止删除该引用表的行。
    • ON DELETE NO ACTION （默认）：没有参照删除动作；引用约束只指定一个约束检查。

    外键建立依赖关系。这<referential action>决定了关系解散时会发生什么。

    例子/隐喻/解释

    对于这个例子，我们将接受社会和经济的共同模型：每个人business都是一家公司，bourgeoisie通过fatcat_owner.

    CREATE TABLE bourgeoisie(
  fatcat_owner varchar(100) PRIMARY KEY
);
INSERT INTO bourgeoisie(fatcat_owner) VALUES
  ( 'Koch Brothers' );

CREATE TABLE business (
  name         varchar(100),
  fatcat_owner varchar(100) REFERENCES bourgeoisie
);
INSERT INTO business(name, fatcat_owner)
  VALUES ('Georgia-Pacific', 'Koch Brothers');

    如果所有business的 es 都直接受到bourgeoisie他们的影响，fatcat_owner那么在工人革命之后，当你清洗fatcat_owners 并拥有一个无阶级的社会时，你会怎么做？

    -- Viva la revolución 
BEGIN;
  DELETE FROM bourgeoisie;
END;

    你有几个选择，

    • 停止革命。在 SQL 用语中，RESTRICT. 有些人认为这是较小的邪恶，但他们通常是错误的。

    • 让它继续下去。如果是这样，当革命发生时，SQL 为您提供了四个选项，

      • SET NULL——留空。谁知道呢，也许资本主义恢复bourgeoisie了，寡头们占了上风fatcat_owners。重要说明，该列必须是NULLABLE(not NOT NULL) 否则这永远不会发生。

      • SET DEFAULT- 也许你有一个DEFAULT处理这个？A DEFAULT可以调用一个函数。也许您的架构已经为革命做好了准备。

      • CASCADE——没有损害控制。如果bourgeoisie去，那么business. 如果一个业务必须有一个fatcat_owner，那么有时丢失数据比在business表中包含一个非业务更有意义。

      • NO ACTION-- 这本质上是一种延迟检查的方法，在 MySQL 中它与 没有什么不同RESTRICT，但在 PostgreSQL 中，你可以做到

            -- Not a real revolution.
    -- requires constraint be DEFERRABLE INITIALLY DEFERRED
    BEGIN;
      SET CONSTRAINTS ALL DEFERRED;
      DELETE FROM bourgeoisie;
      INSERT INTO bourgeoisie VALUES ( 'Putin' );
      UPDATE business SET fatcat_owner = 'Putin';
    END;

        在这样的系统中，仅在事务提交之前验证约束。这可能会导致革命停止，但您可以在交易中恢复——某种程度的“恢复”。

    • 16

  4. 一个简单的助记符是

    ON DELETE of parent CASCADE [by delete] here

    这会告诉您哪些删除（父级的删除）被级联，ON DELETE CASCADE语句在哪里（在子级上），以及什么被删除（子级）。

    • 4

  5. 好吧，也许我们可以合理化语法。让我们以 Python 为例：

    class Parent(self):
    # define parent's fields

class Child(self):    
    # define child's fields
    parent_pk_is_childs_foreign_key = models.ForeignKey(Parent, on_delete=models.CASCADE)

    此行所说的是父级的 on_delete （语句中意外提及），请将删除级联到子级。这就是为什么在子级定义 CASCADE 语句的原因，它标记了那些需要删除的子级

    例如，如果您有另一堂课

    class GrownUpChild(self):    
        # define grown up child's fields
        parent_pk_is_childs_foreign_key = models.ForeignKey(Parent, on_delete=models.DO_NOTHING)

    这个结构将清楚地显示哪些孩子需要被移除（Child），哪些孩子要留下（GrownUpChild），尽管是孤儿

    [编辑：鉴于讨论的上下文，特别是在 on_delete=models.CASCADE 等情况下，] 事实上，由于审计和报告原因，以及恢复意外，离开已删除父母的孩子通常是一种理想的行为删除。[当然企业级软件将围绕这种行为构建，并将已删除的记录标记为已删除=1，而不是实际删除它们，并且也不会将它们包含在前端的任何查询中，减去一些专门设计的报告。另外它还有一个从数据库中清除deleted==1记录的功能，这个功能通常由UI管理员来执行，通常避免数据库管理员方面的任何参与。]

    • -3

相关问题