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

Question

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

255

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

无论如何，我决定使用InnoDB，因为它支持外键。然而，我注意到它允许你为外键设置On Update和On Delete选项。有人能解释一下“Restrict”、“Cascade”和“Set null”在简单例子中哪里可以使用吗？

例如，假设我有一个“user”表，其中包含“userID”。再假设我有一个消息表“message”，它是一个多对多的表格，有2个外键（引用“user”表中相同的主键，“userID”）。在这种情况下设置On Update和On Delete选项有用吗？如果是，我该选择哪个？如果这不是一个好的例子，请提供一个好的例子以说明这些可能有用。

谢谢

- meltuhamy

3个回答

3

您需要将此考虑为应用程序的一部分。通常，您应该设计一个应用程序，而不是仅仅设计数据库（数据库只是应用程序的一部分）。

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

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

个人而言，我会使用InnoDB，因为它不会破坏您的数据（与MyISAM相比），而不是因为它具有FK约束。

- MarkR

3

除了@MarkR的答案之外，需要注意的一件事是许多带有ORM的PHP框架不认可或使用高级DB设置（外键、级联删除、唯一约束），这可能会导致意想不到的行为。

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

- lxa

15

这将是不使用特定ORM的原因。任何在数据库支持方面表现如此糟糕的工具都是不可信的。外键和级联删除或更新是数据库基础而非高级概念，任何关系型数据库都不应该设计没有外键约束的表！ - HLGEM

问题在于它们抛出错误。是否可以限制删除，但是使引擎不生成错误而仍然保持语义？我希望我的程序在执行的同时保护其他数据不被删除。 - TheRealChx101

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- regilero · Accepted Answer

不要犹豫在数据库上设置约束条件。这样可以确保数据库的一致性，这也是使用数据库的好理由之一。特别是如果有多个应用程序请求它（或者只有一个应用程序但使用不同来源的直接模式和批处理模式），更应该如此。

与 postgreSQL 不同，在 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 NO ACTION：与RESTRICT相同。
ON UPDATE CASCADE：通常是最好的：如果您在表COMPANY的一行中更新company_id，则引擎将相应地在所有引用此公司的USER行上更新它（但不会在USER表上激活触发器，警告）。引擎将为您跟踪更改，这很好。
ON UPDATE SET NULL：如果您在表COMPANY的一行中更新company_id，则引擎将相关的USER company_id设置为NULL（应在USER company_id字段中可用）。我无法看出在更新时可以做任何有趣的事情，但我可能错了。

现在来看看ON DELETE方面：

ON DELETE RESTRICT：默认选项：如果您尝试删除表COMPANY中的company_id Id，但至少有一个USER链接到此公司，则引擎将拒绝该操作，可以保护您的数据。
ON DELETE NO ACTION：与RESTRICT相同。
ON DELETE CASCADE：危险选项：如果您删除表COMPANY中的一行，则引擎也将删除相关的USERs。这很危险，但可用于在次要表上进行自动清理（因此可能是您想要的内容，但几乎肯定不适用于COMPANY<->USER示例）。
ON DELETE SET NULL：方便选项：如果您删除COMPANY行，则相关的USERs将自动关系为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语句更改表的操作激活。它们不会对视图的更改或通过不向MySQL服务器传输SQL语句的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在评论中所述，这个问题的情况已经发生了变化。因此，请跟随链接并检查最新的情况（可能在未来再次更改）。