我记得在播客014中听到Joel Spolsky提到他几乎从未使用过外键(如果我没记错的话)。然而,对我来说,它们对于避免数据库中的重复和后续数据完整性问题非常重要。

人们是否有一些可靠的理由(以避免与Stack Overflow原则一致的讨论)?

编辑:“我还没有创建外键的理由,所以这可能是我真正建立一个外键的第一个理由。”


当前回答

我相信有很多应用程序可以使用它,但这不是最好的主意。您不能总是指望您的应用程序能够正确地管理数据库,坦白地说,管理数据库不应该是您的应用程序非常关心的问题。

如果您正在使用关系数据库,那么似乎应该在其中定义一些关系。不幸的是,这种态度(您不需要外键)似乎被许多应用程序开发人员所接受,他们宁愿不为数据完整性等愚蠢的事情所困扰(但需要这样做,因为他们的公司没有专门的数据库开发人员)。通常在这些类型的数据库中,你很幸运只有主键;)

其他回答

有一个很好的理由不使用它们:如果你不了解它们的作用或如何使用它们。

在错误的情况下,外键约束可能导致事故的瀑布式复制。如果有人删除了错误的记录,恢复它可能会成为一项艰巨的任务。

同样,相反,当您需要删除某些内容时,如果设计不当,约束可能会导致各种锁阻止您的操作。

@ emphasis——这正是导致维护噩梦的心态。

为什么,哦,为什么要忽略声明性引用完整性(其中数据至少可以保证一致),而支持所谓的“软件强制”,这充其量是一种薄弱的预防措施。

“在添加记录之前,检查对应的记录是否存在于另一个表中”是业务逻辑。

这里有一些你不希望在数据库中使用它的原因:

If the business rules change, you have to change the database. The database will need to recreate the index in a lot of cases and this is slow on large tables. (Changing rules include: allow guests to post messages or allow users to delete their account despite having posted comments, etc). Changing the database is not as easy as deploying a software fix by pushing the changes to the production repository. We want to avoid changing the database structure as much as possible. The more business logic there is in the database the more you increase the chances of needing to change the databae (and triggering re-indexing). TDD. In unit tests you can substitute the database for mocks and test the functionality. If you have any business logic in your database, you are not doing complete tests and would need to either test with the database or replicate the business logic in code for testing purposes, duplicating the logic and increasing the likelyhood of the logic not working in the same way. Reusing your logic with different data sources. If there is no logic in the database, my application can create objects from records from the database, create them from a web service, a json file or any other source. I just need to swap out the data mapper implementation and can use all my business logic with any source. If there is logic in the database, this isn't possible and you have to implement the logic at the data mapper layer or in the business logic. Either way, you need those checks in your code. If there's no logic in the database I can deploy the application in different locations using different database or flat-file implementations.

跨应用程序生命周期的可维护性和稳定性如何?大多数数据的生命周期都比使用它的应用程序长。关系和数据完整性非常重要,不能寄希望于下一个开发团队在应用程序代码中做出正确的处理。如果您没有使用过不尊重自然关系的脏数据的db,那么您将会使用。数据完整性的重要性将变得非常清楚。

One time when an FK might cause you a problem is when you have historical data that references the key (in a lookup table) even though you no longer want the key available. Obviously the solution is to design things better up front, but I am thinking of real world situations here where you don't always have control of the full solution. For example: perhaps you have a look up table customer_type that lists different types of customers - lets say you need to remove a certain customer type, but (due to business restraints) aren't able to update the client software, and nobody invisaged this situation when developing the software, the fact that it is a foreign key in some other table may prevent you from removing the row even though you know the historical data that references it is irrelevant. After being burnt with this a few times you probably lean away from db enforcement of relationships. (I'm not saying this is good - just giving a reason why you may decide to avoid FKs and db contraints in general)