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

I have a question of interest:

我有一个感兴趣的问题:

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".

我有两个表在mysql和InnoDb。表tbl_a有一个主键a_id;表tbl_b在tbl_a上有一个主b_id和一个外键。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
innodb and foreign keys
instead of
myisam and no foreign keys.
If I just do "NO ACTION" on deletes or updates?

为什么我仍然要使用InnoDb和外键，如果我最终没有使用外键的魔力的话?使用innodb和外键而不是myisam，没有外键还有意义吗?如果我只是在删除或更新上做“无操作”?

I hope you got my point of interest :)

我希望你能理解我的意思

2 个解决方案

#1

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

我想你误解了什么是删除没有行动的意思。这并不意味着要压制外键约束。

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 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.)
它可以级联，意思是删除引用记录。(这对于user_address.user_id之类的东西是有意义的。如果你硬删除一个用户，你可能也想硬删除所有用户的地址。
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".)
它可以设置空值，也就是说，清除引用键。(这可能对file.last_modified_by之类的东西有意义。如果你硬删除一个用户，你可能想要文件的最后修改-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 DELETE and return an error.

如果您指定不执行任何操作，您就是在告诉InnoDB，您不希望它执行这两种操作中的任何一种。所以InnoDB不能帮你解决问题;它所能做的就是拒绝删除并返回一个错误。

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

因此，在DELETE上没有操作实际上与在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.)

(注意:在一些dbms中，在标准SQL中，删除时不执行任何操作与删除限制有一点不同:在这些dbmse中，删除时不执行任何操作意味着“在当前事务中接受删除，但如果我试图在纠正问题之前提交整个事务，则拒绝整个事务”。但InnoDB不支持延迟检查，所以它对删除操作的处理与删除限制完全相同，并且总是立即拒绝删除操作。

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

看到§§14.2.2.5“外键约束”和13.1.17.2“使用外键约束”在MySQL 5.6参考手册。

#2

The foreign key constraint even without ON DELETE / UPDATE CASCADE ensures that if you insert a value into the child table, that it has a correctly matching value in the parent table (or is NULL if 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.

即使在删除/更新级联中没有外键约束，也可以确保如果将一个值插入子表中，它在父表中有一个正确的匹配值(如果FK列是可空的，则为NULL)。尝试将无效值插入子表的FK列将导致约束失败时出现错误，因此您的数据完整性受到保护。

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails

错误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 DELETE clause) will actively prevent deletion 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子句相同)都不会主动阻止删除父行(如果它被任何子表引用)，而不会被动地允许删除它而不影响子行。

#1