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

不要犹豫对数据库进行限制.您将确保拥有一个一致的数据库，这是使用数据库的一个很好的理由.尤其是如果有多个应用程序请求它(或者只有一个应用程序，但使用不同的源使用直接模式和批处理模式).

使用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:the default:如果您try 更新company表中的company_id，则如果一个用户至少链接了该公司，引擎将拒绝该操作.
• ON UPDATE NO ACTION:和限制一样.
• ON UPDATE CASCADE:the best one usually:如果您在company表的一行中更新company_id，引擎将在引用该公司的所有用户行上相应地更新它(但在用户表上没有激活触发器，警告).引擎会帮你追踪变化，这很好.
• ON UPDATE SET NULL:如果您在company表的一行中更新company_id，引擎会将相关用户company_id设置为NULL(应该在USER company_id字段中可用).我在更新中看不到任何有趣的事情，但我可能错了.

现在，在ON DELETE人这边:

• ON DELETE RESTRICT:the default:如果您试图删除company表中的company_id，引擎将拒绝该操作，如果一个用户至少链接了该公司，可以挽救您的生命.
• ON DELETE NO ACTION:和限制一样
• 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:如果删除公司行，相关用户将自动与NULL建立关系.如果Null是您对没有公司的用户的价值，这可能是一种良好的行为，例如，您可能需要将用户作为某些内容的作者保留在应用程序中，但删除公司对您来说不是问题.

通常我的默认值是:ON DELETE RESTRICT ON UPDATE CASCADE.大约有ON DELETE CASCADE个用于跟踪表(日志(log)——不是所有日志(log)——诸如此类)，当主表是包含外键的表的"简单属性"时有ON DELETE SET NULL个，比如用户表的作业(job)表.

Edit

我已经很久没写了.现在我想我应该补充一个重要的警告.MySQL对级联有一个很大的限制.Cascades are not firing triggers.所以如果你对引擎有足够的信心使用触发器，你应该避免级联约束.

• 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

==> See below the last edit, things are moving on this domain

外键操作不会激活触发器.

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

但是我有自己的观点，你应该在糟糕的触发器实现和非常有用的外键约束支持之间做出 Select .一旦你习惯了数据库的一致性，你就会爱上PostgreSQL.

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

正如@IstiaqueAhmed在 comments 中所说，在这个问题上，情况已经发生了变化.因此，请跟随链接，查看真实的最新情况(将来可能会再次发生变化).