外键约束：何时使用 ON UPDATE 和 ON DELETE

Question

我正在使用 MySQL Workbench 设计我的数据库模式，这非常酷，因为您可以绘制图表并转换它们：P

不管怎样，我决定使用 InnoDB，因为它支持外键。我注意到的一件事是，它允许您设置外键的“更新”和“删除”选项。有人可以在一个简单的例子中解释一下“Restrict”、“Cascade”和 set null 可以在哪里使用吗？

例如，假设我有一个 user 表，其中包含 userID。假设我有一个消息表message，它是一个多对多，有 2 个外键（引用相同的主键，userID） 表）。在这种情况下，设置“更新时”和“删除时”选项有用吗？如果是这样，我该选择哪一个？如果这不是一个很好的例子，您能否想出一个很好的例子来说明这些如何有用？

谢谢

Answer 1

除了 @MarkR 答案之外 - 需要注意的一件事是，许多具有 ORM 的 PHP 框架无法识别或使用高级数据库设置（外键、级联删除、唯一约束），这可能会导致意外的行为。

例如，如果您使用 ORM 删除一条记录，并且您的 DELETE CASCADE 将删除相关表中的记录，则 ORM 尝试删除这些相关记录（通常是自动的）将导致错误。

Answer 2

您需要在应用程序的上下文中考虑这一点。一般来说，您应该设计一个应用程序，而不是一个数据库（数据库只是应用程序的一部分）。

考虑您的应用程序应如何响应各种情况。

默认操作是限制（即不允许）操作，这通常是您想要的，因为它可以防止愚蠢的编程错误。然而，DELETE CASCADE 也很有用。这实际上取决于您的应用程序以及您打算如何删除特定对象。

就我个人而言，我会使用 InnoDB，因为它不会破坏你的数据（参见 MyISAM，它会破坏数据），而不是因为它有 FK 约束。

Answer 3

请毫不犹豫地对数据库施加约束。您一定会拥有一个一致的数据库，这是使用数据库的好理由之一。特别是如果您有多个应用程序请求它（或者只有一个应用程序，但具有使用不同源的直接模式和批处理模式）。

对于 MySQL，你没有像 postgreSQL 那样的高级约束，但至少外键约束是相当高级的。

我们将举一个例子，一个公司表和一个包含来自这些公司的人员的用户表

CREATE TABLE COMPANY (
     company_id INT NOT NULL,
     company_name VARCHAR(50),
     PRIMARY KEY (company_id)
) ENGINE=INNODB;

CREATE TABLE USER (
     user_id INT, 
     user_name VARCHAR(50), 
     company_id INT,
     INDEX company_id_idx (company_id),
     FOREIGN KEY (company_id) REFERENCES COMPANY (company_id) ON...
) ENGINE=INNODB;

让我们看一下 ON UPDATE 子句：

• ON UPDATE RESTRICT ：默认：如果您尝试更新表 COMPANY 中的 company_id，如果一个用户至少链接到该公司，引擎将拒绝该操作。
• 更新时不执行：与 RESTRICT 相同。
• ON UPDATE CASCADE：通常是最好的：如果您更新表 COMPANY 的一行中的 company_id，引擎将在引用该 COMPANY 的所有 USER 行上相应地更新它（但不在 USER 表上激活触发器，警告）。引擎会为你跟踪变化，这很好。
• ON UPDATE SET NULL ：如果您更新表 COMPANY 的一行中的 company_id ，引擎会将相关用户的 company_id 设置为 NULL （应该在 USER company_id 字段中可用）。我在更新中看不到任何有趣的事情，但我可能是错的。

现在在ON DELETE一侧：

• ON DELETE RESTRICT：默认：如果您尝试删除表COMPANY中的company_id Id，引擎将如果至少有一个用户链接到该公司，则拒绝该操作可以挽救您的生命。
• ON DELETE NO ACTION ：与 RESTRICT 相同
• ON DELETE CASCADE ：危险：如果您删除表 COMPANY 中的公司行，引擎也会删除相关用户。这是危险的，但可以用于对辅助表进行自动清理（因此它可以是您想要的东西，但肯定不适用于 COMPANY<->USER 示例）
• ON DELETE SET NULL : < em>handful：如果删除 COMPANY 行，相关的 USER 将自动具有 NULL 关系。如果 Null 是您对没有公司的用户的值，这可能是一个很好的行为，例如，您可能需要将用户保留在您的应用程序中，作为某些内容的作者，但删除公司对您来说不是问题。

通常我的默认设置是：ON DELETE RESTRICT ON UPDATE CASCADE。当主表是“简单属性”时，使用一些ON DELETE CASCADE用于跟踪表（日志——不是所有日志——，类似的东西）和ON DELETE SET NULL ' 对于包含外键的表，例如 USER 表的 JOB 表。

编辑

自从我写这篇文章以来已经很长时间了。现在我想我应该添加一个重要警告。 MySQL 在级联方面有一个大的记录限制。 级联不会触发触发器。因此，如果您对该引擎有足够的信心来使用触发器，则应该避免级联约束。

• http://dev.mysql.com/doc/refman/5.6/en /triggers.html

MySQL 触发器仅在 SQL 语句对表进行的更改时激活。它们不会因视图的更改而激活，也不会因不将 SQL 语句传输到 MySQL 服务器的 API 对表所做的更改而激活

• http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

==>请参阅下面的最后一次编辑，该域上的事情正在发生变化

触发器不是由外键操作激活的。

我认为这个问题不会有一天得到解决。外键约束由 InnoDb 存储管理，触发器由 MySQL SQL 引擎管理。两者都分开了。 Innodb是唯一具有约束管理的存储，也许有一天他们会直接在存储引擎中添加触发器，也许不会。

但对于应该在糟糕的触发器实现和非常有用的外键约束支持之间选择哪个元素，我有自己的看法。一旦您习惯了数据库一致性，您就会喜欢 PostgreSQL。

12/2017-更新有关 MySQL 的编辑：

正如 @IstiaqueAhmed 在评论中所述，这个主题的情况已经发生了变化。因此，请点击链接并检查真实的最新情况（将来可能会再次发生变化）。