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

我正在使用MySQL Workbench设计我的数据库模式，这很酷，因为你可以做图表，它可以转换它们:P

不管怎样，我决定使用InnoDB，因为它支持外键。我注意到的一点是，它允许你设置外键的“更新”和“删除”选项。有人能解释一下“限制”，“级联”和设置null可以在一个简单的例子中使用吗?

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

谢谢

190820 次浏览
小开

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

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

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

就我个人而言，我会使用InnoDB，因为它不会丢弃你的数据(c.f. MyISAM，它会)，而不是因为它有FK约束。

小开

除了@MarkR的回答——需要注意的是，许多带有orm的PHP框架不会识别或使用高级DB设置(外键、级联删除、唯一约束)，这可能会导致意想不到的行为。

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

小开
最佳答案

不要犹豫对数据库施加约束。您将确保拥有一个一致的数据库，这是使用数据库的一个很好的理由。特别是当有多个应用程序请求它时(或者只有一个应用程序，但使用了不同的源的直接模式和批处理模式)。

在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;

让我们看看在更新子句:

  • 在更新限制时: 默认的:如果你试图更新COMPANY表中的company_id，如果一个USER至少链接到这个公司，引擎将拒绝该操作。
  • 更新时不做任何操作:与RESTRICT相同。
  • 在级联更新时: 通常是最好的:如果你在COMPANY表的一行中更新了一个company_id，引擎将在引用这个COMPANY的所有USER行中相应地更新它(但没有在USER表上激活触发器，警告)。引擎会为你跟踪变化，这很好。
  • 在更新集为空时:如果你更新COMPANY表一行中的company_id，引擎会将相关的USERs company_id设置为NULL(应该在USER company_id字段中可用)。我没有看到任何有趣的事情与更新，但我可能是错的。

现在在在删除端:

  • 删除限制: 默认的:如果你试图删除COMPANY表中的company_id Id，如果一个USER至少链接到这个公司，引擎将拒绝该操作，可以挽救你的生命。
  • 删除时不做任何操作:与RESTRICT相同
  • 删除级联: 危险的:如果你删除company表中的company行，引擎将删除相关的USERs。这是危险的，但可以用来自动清理副表(所以它可以是你想要的，但肯定不是COMPANY<->USER的例子)
  • 在删除集null时: 一些:如果你删除一个COMPANY行，相关的USERs将自动具有NULL的关系。如果Null是你对没有公司的用户的值，这可能是一个很好的行为，例如，也许你需要在你的应用程序中保留用户，作为一些内容的作者，但删除公司对你来说不是问题。

通常我的默认值是:对更新级联的删除限制。一些ON DELETE CASCADE用于跟踪表(日志——不是所有日志——之类的)，当主表是包含外键的表的“简单属性”时，使用ON DELETE SET NULL，就像USER表的JOB表一样。

编辑

我已经很久没写了。现在我想我应该加上一个重要的警告。MySQL在级联方面有一个很大的限制。级联并不是触发触发器。所以如果你对引擎有足够的信心去使用触发器，你应该避免级联约束。

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

==>看到下面的最后一个编辑，事情正在这个领域上移动

外键操作不能激活触发器。

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

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

12月2017-更新关于MySQL的编辑:

正如@IstiaqueAhmed在评论中所说，这个问题的情况已经改变了。因此，请按照链接查看真实的最新情况(将来可能会再次更改)。


提交答案