首页 文章

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

提问于
浏览 次
152

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

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

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

谢谢

mysql sql database foreign-keys

3 回答

  • 412

    除了@MarkR之外的答案 - 有一点需要注意的是，许多带有ORM的PHP框架无法识别或使用高级数据库设置（外键，级联删除，唯一约束），这可能会导致意外行为 .

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

    回复于
  • 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，如果一个USER至少链接到该公司，则引擎将拒绝该操作 .

    • ON UPDATE NO ACTION ：与RESTRICT相同 .

    • ON UPDATE CASCADE ：通常最好的一个：如果更新表COMPANY中的company_id，引擎将在引用此COMPANY的所有USER行上相应地更新它（但在USER表上没有激活触发器，警告） . 引擎会跟踪你的变化，这很好 .

    • ON UPDATE SET NULL ：如果更新表314行中的company_id，引擎会将相关的USERs company_id设置为NULL（应该在USER company_id字段中可用） . 在更新时我看不出任何有趣的事情，但我可能错了 .

    现在在 ON DELETE 方面：

    • ON DELETE RESTRICT ：默认值：如果您尝试删除表COMPANY中的company_id Id，如果一个USER至少链接到该公司，引擎将拒绝该操作，可以挽救您的生命 .

    • ON DELETE NO ACTION ：与RESTRICT相同

    • ON DELETE CASCADE ：dangerous：如果删除表COMPANY中的公司行，引擎将删除相关的USER . 这很危险，但可用于在辅助表上进行自动清理（因此它可能是您想要的，但肯定不适用于COMPANY < - > USER示例）

    • ON DELETE SET NULL ：handful：如果删除COMPANY行，相关的USER将自动将关系设置为NULL . 如果Null是没有公司的用户的 Value ，这可能是一个很好的行为，例如，您可能需要将用户保留在您的应用程序中，作为某些内容的作者，但删除公司对您来说不是问题 .

    通常我的默认值是： ON DELETE RESTRICT ON UPDATE CASCADE . 当主表是包含外键的表的'simple attribute'时，某些 ON DELETE CASCADE 用于跟踪表（日志 - 不是所有日志 - ，类似的东西）和 ON DELETE SET NULL ，就像USER表的JOB表一样 .

    Edit

    我写这篇文章已经很久了 . 现在我想我应该添加一个重要警告 . MySQL对级联有一个很大的记录限制 . Cascades are not firing triggers . 因此，如果您对该引擎过于自信以使用触发器，则应该避免级联约束 .

    MySQL触发器仅针对SQL语句对表所做的更改进行激活 . 它们不会激活视图中的更改，也不会通过不将API语句传输到MySQL服务器的API所做的更改来激活

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

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

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

    但我对自己应该在糟糕的触发器实现和非常有用的外键约束支持之间选择哪个元素有自己的看法 . 一旦你习惯了数据库的一致性，你就会喜欢PostgreSQL .

    12 / 2017-更新此编辑MySQL：

    正如@IstiaqueAhmed在评论中所述，情况已经发生了变化 . 因此，请点击链接并查看真实的最新情况（将来可能会再次发生变化） .

    回复于
  • 1

    您需要在应用程序的上下文中考虑这一点 . 通常，您应该设计一个应用程序，而不是数据库（数据库只是数据库的一部分）应用） .

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

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

    就个人而言，我会使用InnoDB，因为它不会丢弃你的数据（c.f . MyISAM，它确实如此），而不是因为它有FK约束 .

    回复于

相关问题