为什么要使用外键而不执行删除或更新操作

2022-01-20 00:00:00 foreign-keys mysql innodb myisam

我有一个感兴趣的问题:

I have a question of interest:

我在 mysql 中有 2 个表，其中 <代码>InnoDb.
表 tbl_a 有一个主键，名为 a_id;
表 tbl_b 有一个主 b_id 和一个在 tbl_a.a_id 上带有ON DELETE NO ACTION"的外键.

I have 2 tables in mysql with InnoDb.
table tbl_a has a primary key, named a_id;
table tbl_b has a primary b_id and a foreign key on tbl_a.a_id with "ON DELETE NO ACTION".

+-------------+---------------+---------------+
|  Table Name |  Primary Key  |  Foreign Key  |
+-------------+---------------+---------------+
|    tbl_a    |     a_id      |               |
|    tbl_b    |     b_id      |     a_id      |
+-------------+---------------+---------------+

如果我最终没有真正使用外键的魔力，我为什么还要使用 InnoDb 和外键?
还有没有用的点
innodb 和外键
而不是
myisam 并且没有外键.
如果我只是对删除或更新执行NO ACTION"?

why should I still use InnoDb and foreign keys, if i don't really use the magic of foreign keys in the end in anyway?
Is there still a point of using
innodb and foreign keys
instead of
myisam and no foreign keys.
If I just do "NO ACTION" on deletes or updates?

我希望你能得到我的兴趣:)

I hope you got my point of interest :)

推荐答案

我想你误解了 ON DELETE NO ACTION 的意思.它确实不是意味着抑制外键约束.

I think you're misunderstanding what ON DELETE NO ACTION means. It does not mean to suppress the foreign-key constraint.

当您删除由外键引用的记录时，InnoDB 能够采取自动操作来纠正这种情况:

When you delete a record that is referred to by a foreign key, InnoDB has the ability to take an automatic action to rectify the situation:

  • 可以CASCADE，意思是删除引用记录.(这对于像 user_address.user_id 这样的东西是有意义的.如果您硬删除一个用户，您可能还想硬删除该用户的所有地址.)
  • 可以SET NULL，意思是清除引用键.(这对于 file.last_modified_by 之类的内容可能有意义.如果您硬删除用户，您可能希望文件的 last-modified-by 变得简单未知".)
  • it can CASCADE, meaning, delete the referring record. (This would make sense for something like user_address.user_id. If you hard-delete a user, you probably want to hard-delete all of the user's addresses as well.)
  • it can SET NULL, meaning, clear out the referring key. (This might make sense for something like file.last_modified_by. If you hard-delete a user, you might want the file's last-modified-by to become simply "unknown".)

如果你指定 NO ACTION，你是在告诉 InnoDB 你不希望它采取这些动作中的任何一个.所以 InnoDB 无法为你解决问题；它所能做的就是拒绝 DELETE 并返回错误.

If you specify NO ACTION, you're telling InnoDB that you don't want it to take either of these actions. So InnoDB can't fix the situation for you; all it can do is reject the DELETE and return an error.

因此，ON DELETE NO ACTION实际上与ON DELETE RESTRICT(默认)相同.

As a result, ON DELETE NO ACTION is actually the same as ON DELETE RESTRICT (the default).

(注意:在某些 DBMS 和标准 SQL 中，ON DELETE NO ACTION 与 ON DELETE RESTRICT 有点不同:在那些中，ON DELETENO ACTION 表示接受当前事务中的 DELETE，但如果我在纠正问题之前尝试提交它，则拒绝整个事务".但 InnoDB 不支持延迟检查，所以它将 ON DELETE NO ACTION 视为与 ON DELETE RESTRICT 完全相同，并且始终拒绝 DELETE 立即.)

(Note: in some DBMSes, and in standard SQL, ON DELETE NO ACTION is a bit different from ON DELETE RESTRICT: in those, ON DELETE NO ACTION means "accept the DELETE within the current transaction, but reject the whole transaction if I try to commit it before rectifying the problem". But InnoDB doesn't support deferred checks, so it treats ON DELETE NO ACTION exactly the same as ON DELETE RESTRICT, and always rejects the DELETE immediately.)

参见 §§14.2.2.5外键约束" 和 13.1.17.2 MySQL 5.6 参考手册中的Using FOREIGN KEY Constraints".

See §§14.2.2.5 "FOREIGN KEY Constraints" and 13.1.17.2 "Using FOREIGN KEY Constraints" in the MySQL 5.6 Reference Manual.

相关文章