MySQL 外键约束：何时使用 ON UPDATE 和 ON DELETE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6720050/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Foreign key constraints: When to use ON UPDATE and ON DELETE
提问by meltuhamy
I'm designing my database schema using MySQL Workbench, which is pretty cool because you can do diagrams and it converts them :P
我正在使用 MySQL Workbench 设计我的数据库架构，这非常酷，因为您可以绘制图表并转换它们：P
Anyways, I've decided to use InnoDB because of it's Foreign Key support. One thing I noticed though is that it allows you to set On Update and on Delete options for foreign keys. Can someone explain where "Restrict", "Cascade" and set null could be used in a simple example?
无论如何，我决定使用 InnoDB，因为它支持外键。我注意到的一件事是它允许您为外键设置更新和删除选项。有人能解释一下在一个简单的例子中可以在哪里使用“限制”、“级联”和设置空值吗？
For example, say I have a usertable which includes a userID. And say I have a message table messagewhich is a many-to-many which has 2 foreign keys (which reference the same primary key, userIDin the usertable). Is setting the On Update and On Delete options any useful in this case? If so, which one do I choose? If this isn't a good example, could you please come up with a good example to illustrate how these could be useful?
例如，假设我有一个user包含userID. 假设我有一个message多对多的消息表，它有 2 个外键（userID在user表中引用相同的主键）。在这种情况下，设置 On Update 和 On Delete 选项有用吗？如果是，我该选择哪一个？如果这不是一个很好的例子，你能否提出一个很好的例子来说明这些是如何有用的？
Thanks
谢谢
回答by regilero
Do not hesitate to put constraints on the database. You'll be sure to have a consistent database, and that's one of the good reasons to use a database. Especially if you have several applications requesting it (or just one application but with a direct mode and a batch mode using different sources).
不要犹豫，对数据库施加约束。您将确保拥有一致的数据库，这是使用数据库的一个很好的理由。特别是如果您有多个应用程序请求它（或只有一个应用程序，但具有使用不同来源的直接模式和批处理模式）。
With MySQL you do not have advanced constraints like you would have in postgreSQL but at least the foreign key constraints are quite advanced.
使用 MySQL，您没有像在 postgreSQL 中那样的高级约束，但至少外键约束非常高级。
We'll take an example, a company table with a user table containing people from theses company
我们将举一个例子，一个公司表和一个包含来自这些公司的人的用户表
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;
Let's look at the ON UPDATEclause:
让我们看一下ON UPDATE子句：
- ON UPDATE RESTRICT: the default: if you try to update a company_id in table COMPANY the engine will reject the operation if one USER at least links on this company.
- ON UPDATE NO ACTION: same as RESTRICT.
- ON UPDATE CASCADE: the best one usually: if you update a company_id in a row of table COMPANY the engine will update it accordingly on all USER rows referencing this COMPANY (but no triggers activated on USER table, warning). The engine will track the changes for you, it's good.
- ON UPDATE SET NULL: if you update a company_id in a row of table COMPANY the engine will set related USERs company_id to NULL (should be available in USER company_id field). I cannot see any interesting thing to do with that on an update, but I may be wrong.
- ON UPDATE RESTRICT：默认值：如果您尝试更新表 COMPANY 中的 company_id，如果至少有一个 USER 链接到该公司，则引擎将拒绝该操作。
- ON UPDATE NO ACTION: 与 RESTRICT 相同。
- ON UPDATE CASCADE：通常是最好的：如果您在表 COMPANY 的一行中更新 company_id，引擎将在引用此 COMPANY 的所有 USER 行上相应地更新它（但没有在 USER 表上激活触发器，警告）。引擎会为你跟踪变化，这很好。
- ON UPDATE SET NULL：如果您更新表 COMPANY 的一行中的 company_id，引擎会将相关的 USERs company_id 设置为 NULL（应该在 USER company_id 字段中可用）。我在更新中看不到任何有趣的事情，但我可能是错的。
And now on the ON DELETEside:
现在在删除端：
- ON DELETE RESTRICT: the default: if you try to delete a company_id Id in table COMPANY the engine will reject the operation if one USER at least links on this company, can save your life.
- ON DELETE NO ACTION: same as RESTRICT
- ON DELETE CASCADE: dangerous: if you delete a company row in table COMPANY the engine will delete as well the related USERs. This is dangerous but can be used to make automatic cleanups on secondary tables (so it can be something you want, but quite certainly not for a COMPANY<->USER example)
- ON DELETE SET NULL: handful: if you delete a COMPANY row the related USERs will automatically have the relationship to NULL. If Null is your value for users with no company this can be a good behavior, for example maybe you need to keep the users in your application, as authors of some content, but removing the company is not a problem for you.
- ON DELETE RESTRICT：默认值：如果您尝试删除表 COMPANY 中的 company_id Id，引擎将拒绝该操作，如果至少有一个 USER 链接到该公司，可以挽救您的生命。
- ON DELETE NO ACTION: 同 RESTRICT
- ON DELETE CASCADE：危险：如果您删除表 COMPANY 中的公司行，引擎也会删除相关的用户。这是危险的，但可用于对辅助表进行自动清理（因此它可能是您想要的，但对于 COMPANY<->USER 示例来说肯定不是）
- ON DELETE SET NULL：少数：如果您删除 COMPANY 行，相关用户将自动与 NULL 建立关系。如果 Null 是您对没有公司的用户的价值，这可能是一种良好的行为，例如，您可能需要将用户保留在您的应用程序中，作为某些内容的作者，但删除公司对您来说不是问题。
usually my default is: ON DELETE RESTRICT ON UPDATE CASCADE. with some ON DELETE CASCADEfor track tables (logs--not all logs--, things like that) and ON DELETE SET NULLwhen the master table is a 'simple attribute' for the table containing the foreign key, like a JOB table for the USER table.
通常我的默认值是：ON DELETE RESTRICT ON UPDATE CASCADE。有一些ON DELETE CASCADE用于跟踪表（日志——不是所有日志——，类似的东西），ON DELETE SET NULL当主表是包含外键的表的“简单属性”时，就像用户表的 JOB 表。
Edit
编辑
It's been a long time since I wrote that. Now I think I should add one important warning. MySQL has one big documented limitation with cascades. Cascades are not firing triggers. So if you were over confident enough in that engine to use triggers you should avoid cascades constraints.
我已经很久没有写这个了。现在我想我应该添加一个重要的警告。MySQL 在级联方面有一个很大的记录限制。级联不是触发触发器。因此，如果您对该引擎有足够的信心使用触发器，则应避免级联约束。
MySQL triggers activate only for changes made to tables by SQL statements. They do not activate for changes in views, nor by changes to tables made by APIs that do not transmit SQL statements to the MySQL Server
MySQL 触发器仅在 SQL 语句对表进行的更改时激活。它们不会因视图更改而激活，也不会因不将 SQL 语句传输到 MySQL 服务器的 API 对表所做的更改而激活
==> See below the last edit, things are moving on this domain
==> 见下面最后一次编辑，这个领域的事情正在发生变化
Triggers are not activated by foreign key actions.
外键操作不会激活触发器。
And I do not think this will get fixed one day. Foreign key constraints are managed by the InnoDb storage and Triggers are managed by the MySQL SQL engine. Both are separated. Innodb is the only storage with constraint management, maybe they'll add triggers directly in the storage engine one day, maybe not.
而且我认为这不会有一天得到解决。外键约束由 InnoDb 存储管理，触发器由 MySQL SQL 引擎管理。两者是分开的。Innodb 是唯一有约束管理的存储，也许有一天他们会直接在存储引擎中添加触发器，也许不会。
But I have my own opinion on which element you should choose between the poor trigger implementation and the very useful foreign keys constraints support. And once you'll get used to database consistency you'll love PostgreSQL.
但是我对在糟糕的触发器实现和非常有用的外键约束支持之间应该选择哪个元素有自己的看法。一旦您习惯了数据库一致性，您就会爱上 PostgreSQL。
12/2017-Updating this Edit about MySQL:
12/2017-更新此关于 MySQL 的编辑：
as stated by @IstiaqueAhmed in the comments, the situation has changed on this subject. So follow the link and check the real up-to-date situation (which may change again in the future).
正如@IstiaqueAhmed 在评论中所说，在这个问题上情况已经发生了变化。因此，请按照链接查看最新的真实情况（将来可能会再次更改）。
回答by lxa
Addition to @MarkR answer - one thing to note would be that many PHP frameworks with ORMs would not recognize or use advanced DB setup (foreign keys, cascading delete, unique constraints), and this may result in unexpected behaviour.
除了@MarkR 答案之外 - 需要注意的一件事是，许多带有 ORM 的 PHP 框架无法识别或使用高级数据库设置（外键、级联删除、唯一约束），这可能会导致意外行为。
For example if you delete a record using ORM, and your DELETE CASCADEwill delete records in related tables, ORM's attempt to delete these related records (often automatic) will result in error.
例如，如果您使用 ORM 删除一条记录，并且您DELETE CASCADE将删除相关表中的记录，则 ORM 尝试删除这些相关记录（通常是自动的）将导致错误。
回答by MarkR
You'll need to consider this in context of the application. In general, you should design an application, not a database (the database simply being part of the application).
您需要在应用程序的上下文中考虑这一点。通常，您应该设计一个应用程序，而不是一个数据库（数据库只是应用程序的一部分）。
Consider how your application should respond to various cases.
考虑您的应用程序应如何响应各种情况。
The default action is to restrict (i.e. not permit) the operation, which is normally what you want as it prevents stupid programming errors. However, on DELETE CASCADE can also be useful. It really depends on your application and how you intend to delete particular objects.
默认操作是限制（即不允许）操作，这通常是您想要的，因为它可以防止愚蠢的编程错误。但是，在 DELETE CASCADE 上也很有用。这实际上取决于您的应用程序以及您打算如何删除特定对象。
Personally, I'd use InnoDB because it doesn't trash your data (c.f. MyISAM, which does), rather than because it has FK constraints.
就个人而言，我会使用 InnoDB，因为它不会破坏您的数据（参见 MyISAM，它会），而不是因为它具有 FK 约束。
