From my experience its always better to avoid using FKs in Database Critical Applications. I would not disagree with guys here who say FKs is a good practice but its not practical where the database is huge and has huge CRUD operations/sec. I can share without naming ... one of the biggest investment bank of doesn't have a single FK in databases. These constrains are handled by programmers while creating applications involving DB. The basic reason is when ever a new CRUD is done it has to effect multiple tables and verify for each inserts/updates, though this won't be a big issue for queries affecting single rows but it does create a huge latency when you deal with batch processing which any big bank has to do as daily tasks.

最好避免fk，但它的风险必须由程序员来处理。

使用外键的原因:

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

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

使用外键的其他原因: —可以更好地重用数据库

不使用外键的其他原因: —您试图通过减少重用来锁定客户。

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!

谢谢大家!

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)