澄清数据库是一个没有主键或外键的商业数据库示例。

http://www.geekinterview.com/question_details/18869

有趣的是，技术文档花了很大的篇幅来解释表是如何关联的，用什么列来连接它们等等。

换句话说，它们本可以使用显式声明(DRI)连接表，但它们选择不这样做。

因此，澄清数据库充满了不一致，性能不佳。

但我认为它使开发人员的工作更容易，不必编写代码来处理引用完整性，例如在删除或添加之前检查相关行。

我认为，这就是关系数据库中没有外键约束的主要好处。它使开发变得更容易，至少从不顾一切的角度来看是这样。

外键对于任何关系数据库模型都是必不可少的。

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

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

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.

我只知道Oracle数据库，不知道其他数据库，而且我知道外键对于保持数据完整性是必不可少的。在插入数据之前，需要建立一个数据结构，并且建立正确的数据结构。当这一步完成时——所有的主键和外键都创建好了——工作就完成了!

意思是:孤立的行?不。这辈子都没见过。除非一个糟糕的程序员忘记了外键，或者他在另一个层次上实现了外键。在Oracle的环境中，这两者都是巨大的错误，会导致数据复制、孤儿数据，从而导致数据损坏。我无法想象一个没有强制FK的数据库。在我看来是一片混乱。这有点像Unix权限系统:假设每个人都是root用户。想想混乱吧。

外键是必不可少的，就像主键一样。这就像是说:如果我们移除主键会怎样?那么，整个混乱将会发生。这是什么。不能将主键或外键的职责移到编程级别，但必须移到数据级别。

缺点呢?是的，当然!因为在插入时，会有更多的检查。但是，如果数据完整性比性能更重要，那么这是显而易见的。Oracle上的性能问题更多地与索引有关，索引包含PK和FK。

在DB2中，如果使用mqt(物化查询表)，优化器需要外键约束才能为任何给定的查询选择正确的计划。由于元数据包含基数信息，优化器会大量使用元数据来决定是否使用MQT。

使用外键的原因:

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的所有理由，如果第二部分中的任何理由适用，您可能应该走那条路。