数据库FK约束与程序FK约束 [英] Database FK Constraints vs Programmatic FK Constraints

问题描述

虽然我的目标是MySQL / PHP，但是为了我的问题，我想将它一般应用于任何与现代编程语言结合使用的关系数据库。另一个假设是，语言正在利用一个现代框架，它在某种程度上会明确地处理外键约束或有明确的方法。

我的问题：

• 在数据库本身创建FK约束的优点和缺点




• 从设计的角度来看，如果两者一起使用或会导致冲突？




• 如果不应该一起使用，对于使用哪种方法，什么被认为是最佳实践？

注意：这是一个设计理论问题。由于可以用于满足某个实现的各种技术，我对实现的任何细节都不感兴趣。

解决方案

在数据库本身创建FK约束的优缺点是什么，而不是在应用程序级别管理它们？

在并发环境中，令人惊讶的是难以在应用程序代码中实现参照完整性，从而使它们既正确又性能良好

除非你非常小心地使用锁定，否则你可以参加比赛条件，例如：

• 想象一下，父表中目前有一行，子表中没有相应的行。


• 事务T1在子表中插入一行，但尚未提交。它可以这样做，因为父表中有一个相应的行。


• 事务T2删除父行。 （T1尚未提交）


• T1和T2提交。
$ b $ b
• 此时，您有一个没有父级的子行（即破坏的引用完整性）。

，您可以从两个事务中锁定父行，但是与在DBMS本身中实现的高度优化的FK相比，这可能性能较差。

所有您的客户必须遵守相同的锁定协议（一个行为不当的客户端足以破坏数据）。如果你有多层嵌套FK或菱形FK，复杂度会迅速提高。即使你在触发器中实现引用完整性，你只能解决一个行为不端的客户端问题，但其余的仍然是。

数据库级FKs的另一个好处是它们通常支持引用操作，例如ON DELETE CASCADE。所有这一切都是简单和自我记录的，而不是在应用程序代码中引用的引用完整性。

从设计的角度来看，一起使用或会导致冲突？

您应该始终使用数据库级FK。您也可以使用应用程序级别的预检查，如果这有利于您的用户体验（即，您不想等到实际的INSERT / UPDATE / DELETE警告用户），但应始终编码，如同INSERT /即使您的应用程序级别检查已过，UPDATE / DELETE也可能失败。

如果不应同时使用，最佳实践关于使用哪种方法？

正如我所说，总是使用数据库级FKs。 （可选），您也可以使用顶部的应用程序级FK。

Although I am targeting MySQL/PHP, for the sake of my questions, I'd like to just apply this generally to any relational database that is being used in conjunction with a modern programming language. Another assumption would be that the language is leveraging a modern framework, which, on some level would handle foreign key constraints implicitly or have a means to do so explicitly.

My questions:

• What are the pros and cons of creating FK constraints in the database itself as opposed to managing them at the application level?

• From a design standpoint, should they ever both be used together or would that cause conflict?

• If they should not be used together, what is considered the "best practice" in regards to which approach to use?

Note: This is a design theory question. Because of the wide variety of technology that could be used to satisfy an implementation, I'm not really interested in any specifics regarding an implementation.

解决方案

What are the pros and cons of creating FK constraints in the database itself as opposed to managing them at the application level?

In a concurrent environment, it is surprisingly difficult to implement referential integrity in the application code, such that it is both correct and with good performance.

Unless you very carefully use locking, you are open to race conditions, such as:

• Imagine there is currently one row in the parent table and no corresponding rows in the child.
• Transaction T1 inserts a row in the child table, but does not yet commit. It can do that since there is a corresponding row in the parent table.
• Transaction T2 deletes the parent row. It can do that since there are no child rows from its perspective (T1 hasn't committed yet).
• T1 and T2 commit.
• At this point, you have a child row without parent (i.e. broken referential integrity).

To remedy that, you can lock the parent row from both transactions, but that's likely to be less performant compared to the highly optimized FK implemented in the DBMS itself.

On top of that, all your clients have to adhere to the same "locking protocol" (one misbehaving client is enough to currupt the data). And the complexity rapidly raises if you have several levels of nested FKs or diamond-shaped FKs. Even if you implement referential integrity in triggers, you are only solving the "one misbehaving client" problem, but the rest remains.

Another nice thing about database-level FKs is that they usually support referential actions such as ON DELETE CASCADE. And all that is simple and self-documenting, unlike referential integrity burried inside application code.

From a design standpoint, should they ever both be used together or would that cause conflict?

You should always use database-level FKs. You could also use application level "pre-checks" if that benefits your user experience (i.e. you don't want to wait until the actual INSERT/UPDATE/DELETE to warn the user), but you should always code as if the INSERT/UPDATE/DELETE can fail even if your application-level check has passed.

If they should not be used together, what is considered the "best practice" in regards to which approach to use?

As I stated, always use database-level FKs. Optionally, you may also use application-level FKs "on top" of them.

这篇关于数据库FK约束与程序FK约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！