mysql - update - sql cascade用法




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

不要犹豫,将约束放在数据库上。 你一定会有一个一致的数据库,这是使用数据库的好理由之一。 尤其是如果您有多个应用程序请求它(或者只有一个应用程序,但使用直接模式和使用不同来源的批处理模式)。

有了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,则如果一个用户至少链接到该公司,引擎将拒绝该操作。
  • 更新不采取行动 :与RESTRICT相同。
  • ON UPDATE CASCADE通常最好的一个 :如果更新表COMPANY一行中的company_id,则引擎将在所有引用该COMPANY的USER行(但没有在USER表上激活触发器,警告)上相应地更新它。 引擎会跟踪你的变化,这很好。
  • ON UPDATE SET NULL :如果更新表COMPANY一行中的company_id,则引擎将相关用户company_id设置为NULL(应在USER company_id字段中提供)。 在更新中我看不到任何有趣的事情,但我可能是错的。

现在在ON DELETE方面:

  • ON DELETE RESTRICT缺省值 :如果您尝试删除表COMPANY中的company_id Id,则如果一个用户至少链接到该公司,引擎将拒绝该操作,可以挽救您的生命。
  • ON DELETE NO ACTION :与RESTRICT相同
  • ON DELETE CASCADE危险 :如果删除表COMPANY中的公司行,则引擎将删除相关的用户。 这是很危险的,但可用于在辅助表上进行自动清理(所以它可以是你想要的东西,但肯定不适用于COMPANY < - > USER示例)
  • ON DELETE SET NULL少数 :如果删除COMPANY行,相关用户将自动将关系设置为NULL。 如果Null对于没有公司的用户而言是您的价值,那么这可能是一种好的行为,例如,您可能需要将用户留在应用程序中,作为某些内容的作者,但删除公司对您来说不是问题。

通常我的默认值是: ON DELETE RESTRICT ON UPDATE CASCADE 。 对于轨道表(日志 - 不是所有日志 - 类似的东西)和ON DELETE SET NULL时的ON DELETE CASCADE ,当主表是包含外键的表的'简单属性'时,如JOB表USER表。

编辑

自写这篇文章已经很长时间了。 现在我想我应该添加一个重要的警告。 MySQL对级联有一个大的记录限制。 叶栅不会触发触发器 。 所以如果你对该引擎足够自信以使用触发器,则应该避免级联约束。

MySQL触发器仅用于SQL语句对表进行的更改。 它们不会针对视图中的更改进行激活,也不会更改由不将SQL语句传送到MySQL服务器的API所做的表

==>见下面的最后编辑,事情正在这个域上移动

触发器不会被外键操作激活。

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

但我有自己的意见,你应该选择差的触发器实现和非常有用的外键约束支持之间的元素。 一旦你习惯了数据库的一致性,你会爱上PostgreSQL。

12 / 2017-更新这个关于MySQL的编辑:

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

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

无论如何,我决定使用InnoDB,因为它支持外键。 我注意到的一件事是,它允许您设置“更新”和“删除外键选项”。 有人可以解释“Restrict”,“Cascade”和set null可以在一个简单的例子中使用吗?

例如,假设我有一个包含userIDuser表。 并说我有一个消息表message ,这是一个多对多的有两个外键(它引用相同的主键,在user表中的user userID )。 在这种情况下,设置“更新”和“删除”选项是否有用? 如果是这样,我选择哪一个? 如果这不是一个好例子,请您举个好例子来说明这些可能有用吗?

谢谢


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

考虑你的应用程序应该如何回应各种情况。

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

就我个人而言,我会使用InnoDB,因为它不会垃圾你的数据(比较MyISAM),而不是因为它有FK限制。







foreign-keys