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

Question

我正在使用MySQL Workbench设计数据库架构，这很酷，因为您可以执行图并将其转换为：P

无论如何，由于它具有外键支持，所以我决定使用InnoDB。我注意到的一件事是，它允许您为外键设置On
Update和Delete选项。有人可以在一个简单的示例中解释“限制”，“级联”和设置null的位置吗？

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

谢谢

Answer 1

不要犹豫，对数据库施加约束。您将确保具有一致的数据库，这是使用数据库的充分理由之一。尤其是如果您有多个应用程序要求它（或者只有一个应用程序但使用不同的源但具有直接模式和批处理模式）。

使用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，则如果一个USER至少链接到该公司，则引擎将拒绝该操作。
• 更新时不采取行动 ：与限制相同。
• ON UPDATE CASCADE ： 通常是最好的 ：如果您在表COMPANY的一行中更新company_id，引擎将在引用该COMPANY的所有USER行上相应地对其进行更新（但不会在USER表上激活任何触发器，警告）。引擎会为您跟踪更改，这很好。
• ON UPDATE SET NULL ：如果您在表COMPANY的行中更新company_id，则引擎会将相关的USERs company_id设置为NULL（在USER company_id字段中可用）。我看不到与此更新有关的任何有趣的事情，但我可能是错的。

现在在“ 删除” 侧：

• ON DELETE RESTRICT ： 默认值 ：如果您尝试删除COMPANY表中的company_id ID，则如果一个USER至少可以链接到该公司，则引擎将拒绝该操作，从而可以挽救您的生命。
• 删除时不采取行动 ：与限制相同
• ON DELETE CASCADE ： 危险 ：如果删除表COMPANY中的公司行，则引擎也将删除相关的USER。这很危险，但是可以用来在辅助表上进行自动清理（因此，它可以是您想要的东西，但是肯定不能用于COMPANY <-> USER示例）
• ON DELETE SET NULL ： 少数 ：如果删除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在评论中所述，此问题的情况已发生变化。因此，请点击链接并检查最新的实际情况（将来可能会再次更改）。