小编典典

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

all

我正在使用 MySQL Workbench 设计我的数据库模式,这非常酷,因为您可以制作图表并将它们转换:P

无论如何,我决定使用 InnoDB,因为它支持外键。我注意到的一件事是它允许您为外键设置 On Update 和 on Delete
选项。有人可以在一个简单的例子中解释“限制”、“级联”和设置 null 的位置吗?

例如,假设我有一个user包含userID. 假设我有一个消息表message,它是一个多对多的消息表,它有 2
个外键(userIDuser表中引用相同的主键)。在这种情况下,设置 On Update 和 On Delete
选项有用吗?如果是这样,我该选择哪一个?如果这不是一个很好的例子,你能想出一个很好的例子来说明这些如何有用吗?

谢谢


阅读 61

收藏
2022-06-06

共1个答案

小编典典

不要犹豫,对数据库施加约束。您一定会拥有一个一致的数据库,这是使用数据库的充分理由之一。特别是如果您有多个应用程序请求它(或只有一个应用程序但具有直接模式和使用不同来源的批处理模式)。

使用 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,引擎会将相关的 USERs 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 行,相关的 USER 将自动与 NULL 建立关系。如果 Null 是您对没有公司的用户的价值,这可能是一个好的行为,例如,您可能需要将用户保留在您的应用程序中,作为某些内容的作者,但删除公司对您来说不是问题。

通常我的默认值是: ON DELETE RESTRICT ON UPDATE CASCADE 。有一些ON DELETE CASCADE用于跟踪表(日志——不是所有的日志——之类的东西),ON DELETE SET NULL当主表是包含外键的表的“简单属性”时,比如
USER 表的 JOB 表。

编辑

我已经很久没有写那个了。现在我想我应该添加一个重要的警告。MySQL 对级联有一个很大的记录限制。 级联不是触发触发器
。因此,如果您对该引擎有足够的信心使用触发器,您应该避免级联约束。

MySQL 触发器仅针对 SQL 语句对表所做的更改激活。它们不会因视图的更改而激活,也不会因不将 SQL 语句传输到 MySQL 服务器的 API
对表所做的更改而激活

== > 见下面最后的编辑,这个域正在发生变化

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

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

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

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

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

2022-06-06