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

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

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


当前回答

使用外键的原因:

you won't get Orphaned Rows you can get nice "on delete cascade" behavior, automatically cleaning up tables knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality. FKs give a pretty big hint on what statistics are most important to collect on the database, which in turn leads to better performance they enable all kinds of auto-generated support -- ORMs can generate themselves, visualization tools will be able to create nice schema layouts for you, etc. someone new to the project will get into the flow of things faster since otherwise implicit relationships are explicitly documented

不使用外键的原因:

you are making the DB work extra on every CRUD operation because it has to check FK consistency. This can be a big cost if you have a lot of churn by enforcing relationships, FKs specify an order in which you have to add/delete things, which can lead to refusal by the DB to do what you want. (Granted, in such cases, what you are trying to do is create an Orphaned Row, and that's not usually a good thing). This is especially painful when you are doing large batch updates, and you load up one table before another, with the second table creating consistent state (but should you be doing that sort of thing if there is a possibility that the second load fails and your database is now inconsistent?). sometimes you know beforehand your data is going to be dirty, you accept that, and you want the DB to accept it you are just being lazy :-)

我认为(我不确定!)大多数已建立的数据库都提供了一种指定外键的方法,这种方法不是强制的,只是一些元数据。由于不强制执行消除了不使用fk的所有理由,如果第二部分中的任何理由适用,您可能应该走那条路。

其他回答

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

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

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.

我同意前面的答案,因为它们对维护数据一致性很有用。然而,Jeff Atwood几周前发表了一篇有趣的文章,讨论了规范化和一致性数据的利弊。

简而言之,在处理大量数据时,非规范化数据库可以更快;你可能不关心精确的一致性取决于应用程序,但它迫使你在处理数据时更加小心,因为DB不会。

没有充分的理由不使用它们……除非孤行对你来说不是什么大问题。

我同意德米特里的话,但要补充一点。

我在一个批处理计费系统中工作,需要在30多个表中插入大量的行。我们不允许做数据泵(Oracle),所以我们必须做批量插入。这些表上有外键,但我们已经确保它们不会破坏任何关系。

在插入之前,我们禁用外键约束,这样Oracle就不会一直进行插入。插入成功后,我们重新启用约束。

PS:在一个大型数据库中,一条记录有许多外键和子行数据,有时外键可能不好,您可能希望禁止级联删除。对于在计费系统中的我们来说,如果进行级联删除,将花费太长时间,并且对数据库造成太大负担,因此我们只是在主驱动程序(父)表上使用一个字段将记录标记为坏记录。

更大的问题是:你会戴着眼罩开车吗?如果你开发一个没有参考约束的系统就是这样。请记住,业务需求会改变,应用程序设计会改变,代码中相应的逻辑假设也会改变,逻辑本身也会被重构,等等。一般来说,数据库中的约束是在当代逻辑假设下放置的,对于特定的逻辑断言和假设集似乎是正确的。

在应用程序的整个生命周期中,引用检查和数据检查约束控制通过应用程序收集的数据,特别是当新需求驱动逻辑应用程序更改时。

从实时事务处理系统的角度来看,外键本身不会“提高性能”,也不会显著“降低性能”。然而,在大容量“批处理”系统中,约束检查存在一个聚合代价。这就是实时和批量事务处理的区别;批处理——通过约束检查,按顺序处理的批处理的总成本会对性能造成影响。

在一个设计良好的系统中,数据一致性检查将在处理批处理之前完成(然而,这里也有相关的成本);因此,加载时不需要外键约束检查。事实上,所有的约束,包括外键,都应该暂时禁用,直到批处理完成。

QUERY性能——如果表是在外键上连接的,那么要认识到外键列不是索引的事实(尽管根据定义,各自的主键是索引的)。通过索引一个外键,也就是索引任何键,在索引上连接表有助于提高性能,而不是通过连接一个带外键约束的非索引键。

Changing subjects, if a database is just supporting website display/rendering content/etc and recording clicks, then a database with full constraints on all tables is over kill for such purposes. Think about it. Most websites don’t even use a database for such. For similar requirements, where data is just being recorded and not referenced per say, use an in-memory database, which does not have constraints. This doesn’t mean that there is no data model, yes logical model, but no physical data model.