MySQL 为什么使用外键而不对删除或更新进行操作
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12096790/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Why to use foreign keys with no action on delete or update
提问by Preexo
I have a question of interest:
我有一个感兴趣的问题：
I have 2 tables in mysqlwith InnoDb.
table tbl_ahas a primary key, named a_id;
table tbl_bhas a primary b_idand a foreign key on tbl_a.a_idwith "ON DELETE NO ACTION".
我在mysql 中有 2 个表，带有InnoDb.
表tbl_a有一个主键，名为a_id;
表tbl_b有一个主b_id键和一个外键，tbl_a.a_id带有“ ON DELETE NO ACTION”。
+-------------+---------------+---------------+
| Table Name | Primary Key | Foreign Key |
+-------------+---------------+---------------+
| tbl_a | a_id | |
| tbl_b | b_id | a_id |
+-------------+---------------+---------------+
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
innodband foreign keys
instead of
myisamand no foreign keys.
If I just do "NO ACTION" on deletes or updates?
如果我最终没有真正使用外键的魔力，我为什么还要使用 InnoDb 和外键？
是否仍然使用
innodb和外键
而不是
myisam并且没有外键。
如果我只是NO ACTION在删除或更新时执行“ ”？
I hope you got my point of interest :)
我希望你能引起我的兴趣:)
回答by ruakh
I think you're misunderstanding what ON DELETE NO ACTIONmeans. It does notmean to suppress the foreign-key constraint.
我想你误解了什么ON DELETE NO ACTION意思。它并不意味着打压外键约束。
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:
当您删除由外键引用的记录时，InnoDB 能够采取自动操作来纠正这种情况：
- it can
CASCADE, meaning, delete the referring record. (This would make sense for something likeuser_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 likefile.last_modified_by. If you hard-delete a user, you might want the file's last-modified-by to become simply "unknown".)
- 它可以
CASCADE，意思是，删除引用记录。（这对于类似user_address.user_id. - 它可以
SET NULL，意思是清除引用键。（这对于类似file.last_modified_by.
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 DELETEand return an error.
如果您指定NO ACTION，则是在告诉 InnoDB 您不希望它执行这些操作中的任何一个。所以 InnoDB 不能为你解决这个问题；它所能做的就是拒绝DELETE并返回错误。
As a result, ON DELETE NO ACTIONis actually the same as ON DELETE RESTRICT(the default).
结果，ON DELETE NO ACTION实际上与ON DELETE RESTRICT（默认）相同。
(Note: in some DBMSes, and in standard SQL, ON DELETE NO ACTIONis a bit different from ON DELETE RESTRICT: in those, ON DELETE NO ACTIONmeans "accept the DELETEwithin 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 ACTIONexactly the same as ON DELETE RESTRICT, and always rejects the DELETEimmediately.)
（注意：在某些 DBMS 和标准 SQL 中，ON DELETE NO ACTION与ON DELETE RESTRICT:有点不同：在那些中，ON DELETE NO ACTION表示“接受DELETE当前事务内的，但如果我在纠正问题之前尝试提交它，则拒绝整个事务”。但 InnoDB 没有' 不支持延迟检查，因此它的处理方式与ON DELETE NO ACTION完全相同ON DELETE RESTRICT，并且始终拒绝DELETE立即。）
See §§14.2.2.5 "FOREIGN KEY Constraints"and 13.1.17.2 "Using FOREIGN KEY Constraints"in the MySQL 5.6 Reference Manual.
请参阅MySQL 5.6 参考手册中的§§ 14.2.2.5“外键约束”和13.1.17.2“使用外键约束”。
回答by Michael Berkowski
The foreign key constraint even without ON DELETE / UPDATE CASCADEensures that if you insert a value into the child table, that it has a correctly matching value in the parent table (or is NULLif the FK column is nullable). Attempting to insert an invalid value into the child table's FK column would result in error when the constraint fails, so your data integrity is protected.
即使没有外键约束，也ON DELETE / UPDATE CASCADE可以确保如果将值插入到子表中，它在父表中具有正确匹配的值（或者NULLFK 列是否可以为空）。尝试在子表的 FK 列中插入无效值会在约束失败时导致错误，因此您的数据完整性受到保护。
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
ERROR 1452 (23000): 无法添加或更新子行：外键约束失败
Defining the foreign key constraint also implicitly defines an index on the FK column in the child table, which although you could have manually defined the index, will improve joining performance.
定义外键约束还隐式定义了子表中 FK 列的索引，虽然您可以手动定义索引，但会提高连接性能。
ON DELETE NO ACTION(which is the same as omitting the ON DELETEclause) will actively preventdeletion of a parent row if it is referenced by any child table, not passively allow it to be deleted without affecting child rows.
ON DELETE NO ACTION（与省略ON DELETE子句相同）如果父行被任何子表引用，它将主动阻止删除父行，而不是被动地允许删除它而不影响子行。
