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!

谢谢大家!

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

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

我一直认为不用它们是懒惰的表现。我被教导应该一直这样做。但后来，我没有听乔尔的讨论。他也许有充分的理由，我不知道。

我听到的观点是前端应该有这些业务规则。当您一开始就不应该允许任何破坏约束的插入时，外键会“增加不必要的开销”。我同意吗?不，但我一直都是这么听说的。

编辑:我猜他指的是外键约束，而不是外键这个概念。

“它们会使删除记录变得更加麻烦——你不能删除“主”记录，而其他表中的记录中有外键会违反这种约束。”

重要的是要记住，SQL标准定义了删除或更新外键时所采取的操作。 我所知道的是:

ON DELETE RESTRICT - Prevents any rows in the other table that have keys in this column from being deleted. This is what Ken Ray described above. ON DELETE CASCADE - If a row in the other table is deleted, delete any rows in this table that reference it. ON DELETE SET DEFAULT - If a row in the other table is deleted, set any foreign keys referencing it to the column's default. ON DELETE SET NULL - If a row in the other table is deleted, set any foreign keys referencing it in this table to null. ON DELETE NO ACTION - This foreign key only marks that it is a foreign key; namely for use in OR mappers.

这些相同的操作也适用于ON UPDATE。

默认值似乎取决于您使用的sql server。

我同意德米特里的回答，说得很好。

For those who are worried about the performance overhead FK's often bring, there's a way (in Oracle) you can get the query optimiser advantage of the FK constraint without the cost overhead of constraint validation during insert, delete or update. That is to create the FK constraint with the attributes RELY DISABLE NOVALIDATE. This means the query optimiser ASSUMES that the constraint has been enforced when building queries, without the database actually enforcing the constraint. You have to be very careful here to take the responsibility when you populate a table with an FK constraint like this to make absolutely sure you don't have data in your FK column(s) that violate the constraint, as if you do so you could get unreliable results from queries that involve the table this FK constraint is on.

我通常在数据集市模式中的某些表上使用这种策略，但在集成登台模式中不使用。我要确保复制数据的表已经强制执行了相同的约束，或者ETL例程强制执行了该约束。