Wowowo... Answers everywhere. Actually this is the most complicated topic I have ever encountered. I use FKs when they are needed but on production environment I rarely use them. Here is my whys I rarely use the Fks: 1. Most of the time I am dealing with huge data on small server to improve performance I need to remove the FKs. Because when you have FKs and you do Create, Update or Delete the RDBMS first check if there no constraint violation and if you have huge DB that could be something fatal 2. Sometimes I need to import data from others places and because I am not too sure of how well structured they are, I simply drop the FKs. 3. In case you are dealing with multiple DBs and having reference key in an other DB will not go well(as for now) until you remove the FKs (cross database relations) 4. They was also a case when you write an application which will seat on whatever RDBMS or you want your DB to be exported and imported in any RDBMS system in this case each specific RDBMS system has his own way of dealing with FKs and you will probably be obliged to drop the use of FKs. 5. If you user RDBMS platform (ORMs) you know that some of them offer their own mapping depending on the solution and technicality their offer and you don't care about creating the tables and their FKs. 6. Before the last point will be knowledge to deal with DB that has FKs and the knowledge to write an application that does all the Job without the need of FK 7. Lastly as I started saying it all depend on your scenario, in case knowledge is not a barrier. You will always want to run the best of the best you can get!

谢谢大家!

我也听过这种说法——有些人忘记在外键上放索引，然后抱怨某些操作很慢(因为约束检查可以利用任何索引)。所以总结一下:没有好的理由不使用外键。所有现代数据库都支持级联删除，所以…

我不得不在这里第二多的评论，外键是必要的项目，以确保你有完整的数据。ON DELETE和ON UPDATE的不同选项将允许你绕过一些人们在这里提到的关于它们的使用的“下降”。

我发现在我99%的项目中，我会使用FK来加强数据的完整性，然而，在很少的情况下，我的客户必须保留他们的旧数据，不管它有多糟糕....但后来我花了很多时间写代码，只得到有效的数据，所以它变得毫无意义。

我还认为外键在大多数数据库中是必要的。唯一的缺点(除了强制一致性带来的性能损失之外)是使用外键允许人们编写假定存在功能外键的代码。这绝不应该被允许。

例如，我看到有人编写了一些代码，将插入插入引用表中，然后尝试将插入插入引用表中，而不验证第一次插入是否成功。如果稍后删除外键，则会导致数据库不一致。

您也不能在更新或删除时假设特定的行为。无论是否存在外键，您仍然需要编写代码来执行您想要的操作。如果您假设删除是级联的，但实际上它们不是级联的，那么您的删除将失败。如果您假定对引用列的更新被传播到引用行，但实际上没有，那么您的更新将失败。出于编写代码的目的，最好不要使用这些特性。

如果打开了这些特性，那么您的代码无论如何都会模仿它们，并且会损失一些性能。

所以，总结....如果需要一致的数据库，外键是必不可少的。在您编写的代码中，永远不应假定外键存在或起作用。

在我参与的一个项目中，经常存在隐式关系而不是显式关系，这样可以在同一列上连接多个表。

请看下面的表格

地址

AddressId (PK) EntityId EntityType 城市 状态 国家 等。

EntityType的值可能是Employee、Company、Customer, EntityId指的是您感兴趣的表的主键。

我真的不认为这是最好的方法，但它对这个项目有效。

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)