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

不管怎样,我决定使用InnoDB,因为它支持外键。我注意到的一点是,它允许你设置外键的“更新”和“删除”选项。有人能解释一下“限制”,“级联”和设置null可以在一个简单的例子中使用吗?

例如,假设我有一个用户表,其中包含一个userID。假设我有一个消息表message,它是一个多对多,有两个外键(在用户表中引用相同的主键userID)。设置“更新”和“删除”选项在这种情况下有用吗?如果有,我该选哪一个?如果这不是一个很好的例子,你能想出一个好的例子来说明这些是如何有用的吗?

谢谢


当前回答

不要犹豫对数据库施加约束。您将确保拥有一个一致的数据库,这是使用数据库的一个很好的理由。特别是当有多个应用程序请求它时(或者只有一个应用程序,但使用了不同的源的直接模式和批处理模式)。

在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 : 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 DELETE端:

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 ON UPDATE CASCADE。对轨道表(日志——不是所有日志——之类的)使用一些ON DELETE CASCADE,当主表是包含外键的表的“简单属性”时,ON DELETE SET NULL,就像USER表的JOB表一样。

Edit

我已经很久没写了。现在我想我应该加上一个重要的警告。MySQL在级联方面有一个很大的限制。级联并不是触发触发器。所以如果你对引擎有足够的信心去使用触发器,你应该避免级联约束。

http://dev.mysql.com/doc/refman/5.6/en/triggers.html

MySQL仅在SQL语句对表进行更改时触发activate。它们不会激活视图中的更改,也不会激活不将SQL语句传输到MySQL服务器的api对表的更改

http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

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

外键操作不能激活触发器。

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

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

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

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

其他回答

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

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

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

就我个人而言,我会使用InnoDB,因为它不会丢弃你的数据(c.f. MyISAM,它会),而不是因为它有FK约束。

除了@MarkR的回答——需要注意的是,许多带有orm的PHP框架不会识别或使用高级DB设置(外键、级联删除、唯一约束),这可能会导致意想不到的行为。

例如,如果您使用ORM删除一条记录,并且您的delete CASCADE将删除相关表中的记录,ORM尝试删除这些相关记录(通常是自动的)将导致错误。

不要犹豫对数据库施加约束。您将确保拥有一个一致的数据库,这是使用数据库的一个很好的理由。特别是当有多个应用程序请求它时(或者只有一个应用程序,但使用了不同的源的直接模式和批处理模式)。

在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 : 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 DELETE端:

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 ON UPDATE CASCADE。对轨道表(日志——不是所有日志——之类的)使用一些ON DELETE CASCADE,当主表是包含外键的表的“简单属性”时,ON DELETE SET NULL,就像USER表的JOB表一样。

Edit

我已经很久没写了。现在我想我应该加上一个重要的警告。MySQL在级联方面有一个很大的限制。级联并不是触发触发器。所以如果你对引擎有足够的信心去使用触发器,你应该避免级联约束。

http://dev.mysql.com/doc/refman/5.6/en/triggers.html

MySQL仅在SQL语句对表进行更改时触发activate。它们不会激活视图中的更改,也不会激活不将SQL语句传输到MySQL服务器的api对表的更改

http://dev.mysql.com/doc/refman/5.6/en/stored-program-restrictions.html#stored-routines-trigger-restrictions

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

外键操作不能激活触发器。

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

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

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

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