外钥有什么问题?

I remember hearing Joel Spolsky mention in podcast 014 that he'd barely ever used a foreign key (if I remember correctly). However, to me they seem pretty vital to avoid duplication and subsequent data integrity problems throughout your database.

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

Do people have some solid reasons as to why (to avoid a discussion in lines with Stack Overflow principles)?

对于为什么(为了避免使用栈溢出原则进行讨论)，人们是否有可靠的理由?

Edit: "I've yet to have a reason to create a foreign key, so this might be my first reason to actually set up one."

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

37 个解决方案

#1

Reasons to use Foreign Keys:

使用外键的原因:

• 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
• FKs给出了一个非常重要的提示，告诉我们在数据库中收集什么统计信息最重要，从而获得更好的性能
• 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
• 它们支持各种自动生成的支持——ORMs可以自己生成，可视化工具可以为您创建良好的模式布局，等等
• someone new to the project will get into the flow of things faster since otherwise implicit relationships are explicitly documented
• 项目的新成员将更快地进入事物的流中，因为否则隐式关系将被显式地记录下来

Reasons not to use Foreign Keys:

不使用外键的原因:

• 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
• 你让DB在每个CRUD操作上额外工作，因为它必须检查FK的一致性。如果你有大量的客户流失，这将是一个巨大的成本
• 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?).
• 通过强制关系，FKs指定了必须添加/删除内容的顺序，这可能导致DB拒绝做您想做的事情。(当然，在这种情况下，您要做的是创建一个孤立的行，这通常不是一件好事)。尤其痛苦当你做大的批量更新,和你加载了一个又一个表之前,第二个表创建一致的状态(但你应该做那种事情如果有可能第二次加载失败,现在数据库不一致?)。
• sometimes you know beforehand your data is going to be dirty, you accept that, and you want the DB to accept it
• 有时候你事先知道你的数据会很脏，你接受这个，你希望DB接受它
• you are just being lazy :-)
• 你太懒了。

I think (I am not certain!) that most established databases provide a way to specify a foreign key that is not enforced, and is simply a bit of metadata. Since non-enforcement wipes out every reason not to use FKs, you should probably go that route if any of the reasons in the second section apply.

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

#2

This is an issue of upbringing. If somewhere in your educational or professional career you spent time feeding and caring for databases (or worked closely with talented folks who did), then the fundamental tenets of entities and relationships are well-ingrained in your thought process. Among those rudiments is how/when/why to specify keys in your database (primary, foreign and perhaps alternate). It's second nature.

这是一个成长的问题。如果在你的教育或职业生涯中，你花时间去喂哺和照顾数据库(或者与有经验的人才紧密合作)，那么实体和关系的基本原则就在你的思想过程中根深蒂固。在这些基本原理中，是如何/何时/为什么在数据库中指定键(主键、外键和可能交替键)。这是第二天性。

If, however, you've not had such a thorough or positive experience in your past with RDBMS-related endeavors, then you've likely not been exposed to such information. Or perhaps your past includes immersion in an environment that was vociferously anti-database (e.g., "those DBAs are idiots - we few, we chosen few java/c# code slingers will save the day"), in which case you might be vehemently opposed to the arcane babblings of some dweeb telling you that FKs (and the constraints they can imply) really are important if you'd just listen.

然而，如果您在过去与rdbms相关的工作中没有如此全面或积极的经验，那么您很可能没有接触过这些信息。或者你过去包括浸在一个环境强烈anti-database(例如,“那些dba是白痴——我们几个,我们选择一些java / c#代码吉将保存一天”),在这种情况下,你可能会强烈反对一些白痴的神秘所告诉你颗(和约束可以暗示)真的很重要,如果你只是倾听。

Most everyone was taught when they were kids that brushing your teeth was important. Can you get by without it? Sure, but somewhere down the line you'll have less teeth available than you could have if you had brushed after every meal. If moms and dads were responsible enough to cover database design as well as oral hygiene, we wouldn't be having this conversation. :-)

大多数人小时候都被教导说，刷牙很重要。没有它你能过得下去吗?当然可以，但是如果你每次饭后都刷牙的话，你的牙齿就会少一些。如果妈妈和爸爸足够负责数据库设计和口腔卫生，我们就不会有这样的对话了。:-)

#3

to quote Joe Celko,

引用乔·Celko

"like a size 26 thong, just because you can does not mean you should!"

“就像26码的丁字裤，因为你能穿，并不意味着你应该穿!”

I'm sure there are plenty of applications where you can get away with it, but its' not the best idea. You can't always count on your application to properly manage your database, and frankly managing the database should not be of very much concern to your application.

我相信有很多应用程序可以让你摆脱它，但它不是最好的主意。您不能总是依赖于应用程序来正确地管理数据库，坦率地说，管理数据库不应该是应用程序非常关心的问题。

If you are using a relational database then it seems you ought to have some relationships defined in it. Unfortunately this attitude (you don't need foreign keys) seems to be embraced by a lot of application developers who would rather not be bothered with silly things like data integrity (but need to because their companies don't have dedicated database developers). Usually in databases put together by these types you are lucky just to have primary keys ;)

如果您正在使用关系数据库，那么似乎应该在其中定义一些关系。不幸的是，这种态度(您不需要外键)似乎受到了许多应用程序开发人员的欢迎，他们宁愿不为诸如数据完整性之类的愚蠢事情操心(但需要这样做，因为他们的公司没有专门的数据库开发人员)。通常，在由这些类型组合在一起的数据库中，只有主键是幸运的;

#4

Foreign keys are essential to any relational database model.

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

#5

I always use them, but then I make databases for financial systems. The database is the critical part of the application. If the data in a financial database isn't totally accurate then it really doesn't matter how much effort you put into your code/front-end design. You're just wasting your time.

我总是使用它们，但是我为金融系统建立数据库。数据库是应用程序的关键部分。如果金融数据库中的数据不是完全准确的，那么不管你在代码/前端设计上花了多少精力。你只是在浪费时间。

There's also the fact that multiple systems generally need to interface directly with the database - from other systems that just read data out (Crystal Reports) to systems that insert data (not necessarily using an API I've designed; it may be written by a dull-witted manager who has just discovered VBScript and has the SA password for the SQL box). If the database isn't as idiot-proof as it can possibly be, well - bye bye database.

还有一个事实是，多个系统通常需要直接与数据库交互——从读取数据的其他系统(Crystal Reports)到插入数据的系统(不一定使用我设计的API);它可能是由一个愚蠢的经理编写的，他刚刚发现了VBScript，并且拥有SQL box的SA密码)。如果数据库不像它可能的那样可以防止白痴行为，那么再见，数据库。

If your data is important, then yes, use foreign keys, create a suite of stored procedures to interact with the data, and make the toughest DB you can. If your data isn't important, why are you making a database to begin with?

如果您的数据很重要，那么可以使用外键，创建一组存储过程来与数据交互，并尽可能使用最硬的DB。如果您的数据不重要，为什么要首先创建一个数据库呢?

#6

Foreign keys complicate automated testing

Suppose you're using foreign keys. You're writing an automated test that says "when I update a financial account, it should save a record of the transaction." In this test, you're only concerned with two tables: accounts and transactions.

假设你用的是外键。您正在编写一个自动测试，该测试说:“当我更新财务帐户时，它应该保存交易记录。”在这个测试中，您只关心两个表:帐户和事务。

However, accounts has a foreign key to contracts, and contracts has a fk to clients, and clients has a fk to cities, and cities has a fk to states.

然而，客户对合同有外国钥匙，合同对客户有fk，客户对城市有fk，城市对州有fk。

Now the database will not allow you to run your test without setting up data in four tables that aren't related to your test.

现在，数据库将不允许您运行您的测试而在与您的测试无关的四个表中设置数据。

There are at least two possible perspectives on this:

至少有两种可能的观点:

• "That's a good thing: your test should be realistic, and those data constraints will exist in production."
• “这是一件好事:您的测试应该是真实的，这些数据约束将存在于生产中。”
• "That's a bad thing: you should be able to unit test pieces of the system without involving other pieces. You can add integration tests for the system as a whole."
• “这是一件坏事:您应该能够对系统的各个部分进行单元测试，而不涉及其他部分。”您可以为整个系统添加集成测试。

It may also be possible to temporarily turn off foreign key checks while running tests. MySQL, at least, supports this.

还可以在运行测试时暂时关闭外键检查。至少MySQL支持这一点。

---

Update: I always use foreign keys now. My answer to the objection "they complicated testing" is "write your unit tests so they don't need the database at all. Any tests that use the database should use it properly, and that includes foreign keys. If the setup is painful, find a less painful way to do the setup."

更新:我现在总是使用外键。我对反对意见“他们复杂的测试”的回答是“编写单元测试，这样他们根本不需要数据库。”任何使用数据库的测试都应该正确地使用它，包括外键。如果设置是痛苦的，找到一种不那么痛苦的方式来进行设置。

#7

@imphasing - this is exactly the kind of mindset that causes maintenance nightmares.

@imphasing——这正是导致维护噩梦的那种心态。

Why oh why would you ignore declarative referential integrity, where the data can be guaranteed to be at least consistent, in favour of so called "software enforcement" which is a weak preventative measure at best.

为什么你会忽略声明性引用完整性，在这里数据可以保证至少是一致的，支持所谓的“软件强制执行”，这是一种弱的预防措施。

#8

"They can make deleting records more cumbersome - you can't delete the "master" record where there are records in other tables where foreign keys would violate that constraint."

“它们会使删除记录变得更麻烦——您不能删除“主”记录，因为在其他表中有记录，而外键会违反此约束。”

It's important to remember that the SQL standard defines actions that are taken when a foreign key is deleted or updated. The ones I know of are:

重要的是要记住，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.
• 在“删除限制”—防止删除列中有键的其他表中的任何行。这就是Ken Ray所描述的。
• 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.
• 在DELETE SET默认值中——如果删除了另一个表中的一行，则将引用它的任何外键设置为列的默认值。
• ON DELETE SET NULL - If a row in the other table is deleted, set any foreign keys referencing it in this table to null.
• 在DELETE SET NULL—如果删除了另一个表中的一行，则将该表中引用它的任何外键设置为NULL。
• ON DELETE NO ACTION - This foreign key only marks that it is a foreign key; namely for use in OR mappers.
• 在删除时不执行任何操作——此外键仅标记为外键;即用于或制图者。

These same actions also apply to ON UPDATE.

这些动作也适用于更新。

The default seems to depend on which SQL server you're using.

默认情况似乎取决于您使用的是哪个SQL服务器。

#9

There's one good reason not to use them: If you don't understand their role or how to use them.

有一个很好的理由不去使用它们:如果你不了解它们的作用或如何使用它们。

In the wrong situations, foreign key constraints can lead to waterfall replication of accidents. If somebody removes the wrong record, undoing it can become a mammoth task.

在错误的情况下，外部关键约束可能导致事故的瀑布式复制。如果有人删除了错误的记录，取消记录将成为一项艰巨的任务。

Also, conversely, when you need to remove something, if poorly designed, constraints can cause all sorts of locks that prevent you.

同样，相反地，当您需要删除某些东西时，如果设计不好，约束可能会导致各种类型的锁阻止您。

#10

There are no good reasons not to use them... unless orphaned rows aren't a big deal to you I guess.

没有理由不使用它们……除非孤立的行对你来说没什么大不了。

#11

Bigger question is: would you drive with a blindfold on? That’s how it is if you develop a system without referential constraints. Keep in mind, that business requirements change, application design changes, respective logical assumptions in the code changes, logic itself can be refactored, and so on. In general, constraints in databases are put in place under contemporary logical assumptions, seemingly correct for particular set of logical assertions and assumptions.

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

Through the lifecycle of an application, referential and data checks constraints police data collection via the application, especially when new requirements drive logical application changes.

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

To the subject of this listing - a foreign key does not by itself "improve performance", nor does it "degrade performance" significantly from a standpoint of real-time transaction processing system. However, there is an aggregated cost for constraint checking in HIGH volume "batch" system. So, here is the difference, real-time vs. batch transaction process; batch processing - where aggreated cost, incured by constraint checks, of a sequentially processed batch poses a performance hit.

对于这个列表的主题——外键本身不会“提高性能”，也不会从实时事务处理系统的角度“显著降低性能”。但是，在大容量的“批处理”系统中，约束检查有一个聚合成本。这就是实时处理和批量处理的区别;成批处理——连续处理的成批的聚合成本(通过约束检查)会对性能造成影响。

In a well designed system, data consistency checks would be done "before" processing a batch through (nevertheless, there is a cost associated here also); therefore, foreign key constraint checks are not required during load time. In fact all constraints, including foreign key, should be temporarily disabled till the batch is processed.

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

QUERY PERFORMANCE - if tables are joined on foreign keys, be cognizant of the fact that foreign key columns are NOT INDEXED (though the respective primary key is indexed by definition). By indexing a foreign key, for that matter, by indexing any key, and joining tables on indexed helps with better performances, not by joining on non-indexed key with foreign key constraint on it.

查询性能——如果表是与外键连接的，请注意外键列没有被索引的事实(尽管根据定义，对应的主键是索引的)。通过使用一个外键(就此而言)，通过任何键，通过在索引上连接表，可以提高性能，而不是通过将非索引键与外键约束连接起来。

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.

改变主题，如果一个数据库只是支持网站显示/呈现内容/等等和记录点击，那么一个对所有表都有完整约束的数据库就会因为这样的目的而被扼杀。想想。大多数网站甚至不使用数据库。对于类似的需求，如果只是记录数据而不是每个实例引用数据，那么使用内存中的数据库，它没有约束。这并不意味着没有数据模型，是逻辑模型，但是没有物理数据模型。

#12

Additional Reason to use Foreign Keys: - Allows greater reuse of a database

使用外键的其他原因:-允许更大程度地重用数据库。

Additional Reason to NOT use Foreign Keys: - You are trying to lock-in a customer into your tool by reducing reuse.

不使用外键的额外原因:-您试图通过减少重用将客户锁定到您的工具中。

#13

I agree with the previous answers in that they are useful to mantain data consistency. However, there was an interesting post by Jeff Atwood some weeks ago that discussed the pros and cons of normalized and consistent data.

我同意前面的答案，因为它们有助于保持数据一致性。然而，几周前Jeff Atwood有一篇有趣的文章讨论了规范化和一致性数据的优缺点。

In a few words, a denormalized database can be faster when handling huge amounts of data; and you may not care about precise consistency depending on the application, but it forces you to be much more careful when dealing with data, as the DB won't be.

简单地说，当处理大量数据时，非规范化数据库可以更快;您可能不关心依赖于应用程序的精确一致性，但它迫使您在处理数据时更加小心，因为DB不会。

#14

I know only Oracle databases, no other ones, and I can tell that Foreign Keys are essential for maintaining data integrity. Prior to inserting data, a data structure needs to be made, and be made correctlty. When that is done - and thus all primary AND foreign keys are created - the work is done !

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

Meaning : orphaned rows ? No. Never seen that in my life. Unless a bad programmer forgot the foreign key, or if he implemented that on another level. Both are - in context of Oracle - huge mistakes, which will lead to data duplication, orphan data, and thus : data corruption. I can't imagine a database without FK enforced. It looks like chaos to me. It's a bit like the Unix permission system : imagine that everybody is root. Think of the chaos.

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

Foreign Keys are essential, just like Primary Keys. It's like saying : what if we removing Primary Keys ? Well, total chaos is going to happen. That's what. You may not move the primary or foreign key responsibility to the programming level, it must be at the data level.

外键很重要，就像主键一样。这就像是在说:如果我们删除主键呢?完全混乱会发生。这是什么。您可能不会将主键或外键职责移动到编程级别，它必须位于数据级别。

Drawbacks ? Yes, absolutely ! Because on insert, a lot more checks are going to be happening. But, if data integrity is more important than performance, it's a no-brainer. The problem with performance on Oracle is more related to indexes, which come with PK and FK's.

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

#15

"Before adding a record, check that a corresponding record exists in another table" is business logic.

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

Here are some reasons you don't want this in the database:

以下是一些你不希望在数据库中出现的原因:

1. 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).

   如果业务规则发生更改，则必须更改数据库。数据库将需要在许多情况下重新创建索引，这在大型表上是很慢的。(修改规则包括:允许客人发布信息或允许用户删除自己的账户，尽管用户已经发布了评论等等)。

2. 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).

   更改数据库并不像通过将更改推送到生产存储库来部署软件修复程序那样容易。我们希望尽可能避免更改数据库结构。数据库中的业务逻辑越多，就越有可能需要更改数据库(并触发重新索引)。

3. 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.

   TDD。在单元测试中，可以用数据库代替mock并测试功能。如果您的数据库中有任何业务逻辑，那么您就没有完成完整的测试，并且需要对数据库进行测试，或者在代码中复制业务逻辑以进行测试，重复逻辑，并增加逻辑的可能性，而不是以相同的方式工作。

4. 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.

   使用不同的数据源重用逻辑。如果数据库中没有逻辑，我的应用程序可以从数据库的记录创建对象，从web服务、json文件或任何其他来源创建对象。我只需要交换数据映射器实现，并可以使用我的所有业务逻辑与任何源。如果数据库中存在逻辑，那么这是不可能的，您必须在数据映射层或业务逻辑中实现逻辑。无论哪种方式，都需要在代码中进行这些检查。如果数据库中没有逻辑，我可以使用不同的数据库或平面文件实现将应用程序部署到不同的位置。

#16

They can make deleting records more cumbersome - you can't delete the "master" record where there are records in other tables where foreign keys would violate that constraint. You can use triggers to have cascading deletes.

它们可以使删除记录变得更麻烦——您不能删除“主”记录，在其他表中有记录的外键会违反这个约束。可以使用触发器进行级联删除。

If you chose your primary key unwisely, then changing that value becomes even more complex. For example, if I have the PK of my "customers" table as the person's name, and make that key a FK in the "orders" table", if the customer wants to change his name, then it is a royal pain... but that is just shoddy database design.

如果您不明智地选择了主键，那么更改该值将变得更加复杂。例如，如果我将“客户”表的PK作为人名，并在“orders”表中做一个FK，如果客户想更改他的名字，那么这就是皇家的痛苦……但这只是劣质的数据库设计。

I believe the advantages in using fireign keys outweighs any supposed disadvantages.

我相信使用fireign键的优点胜过任何假设的缺点。

#17

Verifying foreign key constraints takes some CPU time, so some folks omit foreign keys to get some extra performance.

验证外键约束需要一些CPU时间，因此有些人会省略外键以获得一些额外的性能。

#18

The Clarify database is an example of a commercial database that has no primary or foreign keys.

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

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

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

The funny thing is, the technical documentation goes to great lengths to explain how tables are related, what columns to use to join them etc.

有趣的是，技术文档非常详细地解释了表是如何关联的，要使用哪些列来连接它们等等。

In other words, they could have joined the tables with explicit declarations (DRI) but they chose not to.

换句话说，他们可以用显式声明(DRI)加入表格，但他们选择了不加入。

Consequently, the Clarify database is full of inconsistencies and it underperforms.

因此，澄清数据库中充满了不一致性，并且表现不佳。

But I suppose it made the developers job easier, not having to write code to deal with referential integrity such as checking for related rows before deleting, adding.

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

And that, I think, is the main benefit of not having foreign key constraints in a relational database. It makes it easier to develop, at least that is from a devil-may-care point of view.

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

#19

I have heard this argument too - from people who forgot to put an index on their foreign keys and then complained that certain operations were slow (because constraint checking could take advantage of any index). So to sum up: There is no good reason not to use foreign keys. All modern databases support cascaded deletes, so...

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

#20

The argument I have heard is that the front-end should have these business rules. Foreign keys "add unnecessary overhead" when you shouldn't be allowing any insertions that break your constraints in the first place. Do I agree with this? No, but that is what I have always heard.

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

EDIT: My guess is he was referring to foreign key constraints, not foreign keys as a concept.

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

#21

To me, if you want to go by the ACID standards, it is critical to have foreign keys to ensure referential integrity.

对我来说，如果您想按照ACID标准进行操作，那么使用外键来确保引用完整性是至关重要的。

#22

I have to second most of the comments here, Foreign Keys are necessary items to ensure that you have data with integrity. The different options for ON DELETE and ON UPDATE will allow you to get around some of the "down falls" that people mention here regarding their use.

在这里，我不得不对大多数评论进行补充，外键是确保数据完整性的必要项。删除和更新的不同选项可以让你绕过人们在这里提到的关于他们使用的“下降瀑布”。

I find that in 99% of all my projects I will have FK's to enforce the integrity of the data, however, there are those rare occasions where I have clients that MUST keep their old data, regardless of how bad it is....but then I spend a lot of time writing code that goes in to only get the valid data anyway, so it becomes pointless.

我发现在99%的所有项目我有颗执行数据的完整性,然而,也有一些罕见的场合,我有客户,必须保持他们的旧数据,不管它有多坏....但后来我花了很多时间编写代码，只获取有效的数据，因此变得毫无意义。

#23

How about maintainability and constancy across application life cycles? Most data has a longer lifespan than the applications that make use of it. Relationships and data integrity are much too important to leave to the hope that the next dev team gets it right in the app code. If you haven't worked on a db with dirty data that doesn't respect the natural relationships, you will. The importance of data integrity will then become very clear.

在应用程序生命周期中，可维护性和稳定性如何?大多数数据的使用寿命都比应用程序长。关系和数据完整性太重要了，不能指望下一个开发团队在应用程序代码中找到它。如果你没有在数据库中使用不尊重自然关系的脏数据，你会。数据完整性的重要性将变得非常明显。

#24

I also think that foreign keys are a necessity in most databases. The only drawback (besides the performance hit that comes with having enforced consistence) is that having a foreign key allows people to write code that assumes there is a functional foreign key. That should never be allowed.

我还认为，在大多数数据库中，外键是必需的。惟一的缺点(除了强制一致性带来的性能问题之外)是，拥有外键允许人们编写假定有一个功能外键的代码。这是不应该允许的。

For example, I've seen people write code that inserts into the referenced table and then attempts inserts into the referencing table without verifying the first insert was successful. If the foreign key is removed at a later time, that results in an inconsistent database.

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

You also don't have the option of assuming a specific behavior on update or delete. You still need to write your code to do what you want regardless of whether there is a foreign key present. If you assume deletes are cascaded when they are not, your deletes will fail. If you assume updates to the referenced columns are propogated to the referencing rows when they are not, your updates will fail. For the purposes of writing code, you might as well not have those features.

您也不能在更新或删除时假定特定的行为。无论是否存在外键，您仍然需要编写代码来执行所需的操作。如果假设删除操作是级联的，那么删除操作将失败。如果您假设引用列的更新被提交到引用行，而它们不是，那么您的更新将失败。出于编写代码的目的，您可能没有这些特性。

If those features are turned on, then your code will emulate them anyway and you'll lose a little performance.

如果这些特性被打开，那么无论如何您的代码都将模拟它们，您将丢失一些性能。

So, the summary.... Foreign keys are essential if you need a consistent database. Foreign keys should never be assumed to be present or functional in code that you write.

所以,总结....如果需要一致的数据库，那么外键是必不可少的。在编写的代码中，不应该假定外键是当前的或函数的。

#25

I echo the answer by Dmitriy - very well put.

我同意Dmitriy的答案——说得很好。

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.

对于那些担心FK带来的性能开销的人来说，有一种方法(在Oracle中)可以获得FK约束的查询optimiser优势，而不需要在插入、删除或更新期间进行约束验证的成本开销。即使用属性禁用NOVALIDATE创建FK约束。这意味着查询optimiser假设在构建查询时强制执行约束，而数据库实际上不强制执行约束。你必须非常小心的责任当你表填充一个颗这样的约束,绝对确保你在颗列没有数据(s)违反约束,如果你这样做你可以得到可靠的结果从表的查询涉及到这颗约束。

I usually use this strategy on some tables in my data mart schema, but not in my integrated staging schema. I make sure the tables I am copying data from already have the same constraint enforced, or the ETL routine enforces the constraint.

我通常在数据集市模式中的某些表上使用这种策略，但在集成阶段模式中不使用。我确保复制数据的表已经具有相同的约束，或者ETL例程强制约束。

#26

Many of the people answering here get too hung up on the importance of referential integrity implemented via referential constraints. Working on large databases with referential integrity just does not perform well. Oracle seems particularly bad at cascading deletes. My rule of thumb is that applications should never update the database directly and should be via a stored procedure. This keeps the code base inside the database, and means that the database maintains its integrity.

在这里回答这个问题的许多人都过于关注通过引用约束实现的引用完整性的重要性。在具有引用完整性的大型数据库上工作并不好。在级联删除方面，Oracle似乎特别糟糕。我的经验是，应用程序不应该直接更新数据库，而应该通过存储过程进行更新。这将把代码库保存在数据库中，并意味着数据库保持其完整性。

Where many applications may be accessing the database, problems do arise because of referential integrity constraints but this is down to a control.

当许多应用程序访问数据库时，由于引用完整性约束而出现问题，但这取决于控件。

There is a wider issue too in that, application developers may have very different requirements that database developers may not necessarily be that familiar with.

还有一个更广泛的问题，应用程序开发人员可能有非常不同的需求，数据库开发人员可能并不熟悉这些需求。

#27

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.

根据我的经验，最好避免在数据库关键型应用程序中使用FKs。我不同意这里的人说FKs是一个很好的实践，但是当数据库很大，并且有大量的CRUD操作/sec时，它是不实用的。我可以分享而不点名……最大的投资银行之一在数据库中没有一个FK。这些限制由程序员在创建涉及DB的应用程序时处理。的基本原因是当过一个新的CRUD是它影响多个表为每个插入/更新和验证,但这不会是一个大问题对于影响个别行但它的查询创建一个巨大的延迟处理批处理时,任何大型银行作为日常任务。

Its better to avoid FKs but its risk has to be handled by programmers.

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

#28

If you are absolutey sure, that the one underlying database system will not change in the future, I would use foreign keys to ensure data integrity.

如果您绝对确信，一个底层数据库系统将来不会改变，我将使用外键来确保数据的完整性。

But here is another very good real-life reason not to use foreign keys at all:

但这里有另一个很好的现实理由，完全不使用外键:

You are developing a product, which should support different database systems.

您正在开发一个产品，该产品应该支持不同的数据库系统。

If you are working with the Entity Framework, which is able to connect to many different database systems, you may also want to support "open-source-free-of-charge" serverless databases. Not all of these databases may support your foreign key rules (updating, deleting rows...).

如果您正在使用能够连接到许多不同数据库系统的实体框架，那么您可能还希望支持“免费开放源代码”的无服务器数据库。并非所有这些数据库都支持外键规则(更新、删除行…)。

This can lead to different problems:

这会导致不同的问题:

1.) You may run into errors, when the database structure is created or updated. Maybe there will only be silent errors, because your foreign keys are just ignored by the database system.

1)。当创建或更新数据库结构时，您可能会遇到错误。可能只会出现静默错误，因为您的外键只是被数据库系统忽略了。

2.) If you rely on foreign keys, you will propably make less or even no data integrity checks in your business logic. Now, if the new database system does not support these foreign key rules or just behaves in a different way, you have to rewrite your business logic.

2)。如果您依赖于外键，您可能会在业务逻辑中减少甚至不进行数据完整性检查。现在，如果新的数据库系统不支持这些外键规则，或者只是以不同的方式运行，则必须重写业务逻辑。

You may ask: Who needs different database systems? Well, not everybody can afford or wants a full blown SQL-Server on his machine. This is software, which needs to be maintained. Others already have invested time and money in some other DB system. Serverless database are great for small customers on only one machine.

您可能会问:谁需要不同的数据库系统?好吧，不是每个人都能负担得起或者想要一台完整的sql服务器在他的机器上。这是需要维护的软件。其他人已经在其他一些DB系统上投入了时间和金钱。对于只有一台机器的小客户来说，没有服务器的数据库非常有用。

Nobody knows, how all of these DB systems behave, but your business logic, with integrity checks, always stays the same.

没有人知道所有这些DB系统的行为，但是您的业务逻辑，通过完整性检查，始终保持不变。

#29

I always thought it was lazy not to use them. I was taught it should always be done. But then, I didnt listen to Joel's discussion. He may have had a good reason, I don't know.

我一直认为不使用它们是懒惰的。我被教导说，这是必须的。但是，我没有听乔尔的讨论。我不知道他有什么好的理由。

#30

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)

有一次FK可能会给您带来一个问题，那就是当您拥有引用该键(在查找表中)的历史数据时，即使您不再需要可用的密钥。显然，解决方案是预先把事情设计得更好，但我想到的是现实世界中的情况，在这种情况下，你并不总是能够控制整个解决方案。例如:也许你有一个查找表的customer_type列出不同类型的客户——假设你需要删除一个特定的客户类型,但(由于业务限制)无法更新客户端软件,没有人invisage这种情况在开发软件时,它是其他表的外键可能阻止你删除行,即使你知道引用它的历史数据是无关紧要的。在经历了这几次之后，你可能会倾向于依赖于关系的执行。(我并不是说这很好——只是给你一个理由，为什么你可能决定避免FKs和db的普遍限制)