您绝对需要数据库中的外键吗？
我想知道外键在数据库中到底有多大用处。本质上，如果开发人员知道不同表依赖哪些键，他们就可以像有外键一样编写查询，对吧？
另外，我确实看到外键约束如何帮助防止数据完整性方面的各种错误，但是例如，程序员在保持数据完整性方面做得很好，外键到底有多大必要？
I was wondering how useful foreign keys really are in a database. Essentially, if the developers know what keys the different tables depend on, they can write the queries just as though there was a foreign key, right?
Also, I do see how to foreign-key constraints help prevent all sorts of bugs with data integrity, but say for example, the programmers do a good job of preserving data integrity, how necessary are foreign keys really?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（12）
如果您不关心引用完整性那么您是对的。但是......你应该关心引用完整性。
问题是人们会犯错误。电脑则不然。
关于您的评论：
有人最终会犯错误。没有人是完美的。此外，如果您引入新人，您并不总是确定他们是否有能力编写“完美”代码。
除此之外，您将失去执行级联删除的能力以及定义外键允许的许多其他功能。
If you don't care about referential integrity then you are right. But.... you should care about referential integrity.
The problem is that people make mistakes. Computers do not.
Regarding your comment:
Someone will eventually make a mistake. No one is perfect. Also if you bring someone new in you aren't always sure of their ability to write "perfect" code.
In addition to that you lose the ability to do cascading deletes and a number of other features that having defined foreign keys allow.
我认为假设程序员始终保持数据完整性是一个危险的假设。
您没有理由不创建外键，并且能够保证完整性而不是仅仅希望完整性就足够了。
I think that assuming that programmers will always preserve data integrity is a risky assumption.
There's no reason why you wouldn't create foreign keys, and being able to guarantee integrity instead of just hoping for integrity is reason enough.
外键作为确保完整性的一种手段是非常宝贵的，即使您相信您的开发人员永远不会（！）犯错误，但拥有这些错误的成本通常也是值得的。
外键也可以用作文档，因为您可以看到什么与什么相关。这些信息通常也被工具使用，例如用于生成报告、从表定义创建数据集、对象关系映射器等。即使您现在不使用其中任何一个，拥有 FK 也会让您更轻松地走上这条路之后。
外键还允许您定义级联规则，例如，当删除一个表中的一行时，可以使用这些规则来删除相关表中的关联记录。
只有当您的负载高得离谱时，您才应该考虑绕过 FK。
编辑：更新答案以包括其他答案（报告、级联）中的要点。
Foreign keys are invaluable as a means of ensuring integrity, and even if you trust your developers to never (!) make errors the cost of having them is usually well worth it.
Foreign keys also serve as documentation, in that you can see what relates to what. This information is typically also used by tools, such as for generating reports, creating data sets from table definitions, object-relational mappers, etc. Even if you do not use any of these today, having FKs will make it easier to tread that path later.
Foreign keys also allow you to define cascade rules, which e.g. can be used to to delete associated records in related tables when a row in one table is deleted.
Only if you have ridiculously high loads should you consider bypassing FKs.
Edit: updated answer to include points from other answers (reports, cascades).
在数据库中不使用引用完整性就像在汽车中不使用安全带一样。它将为您提供从 A->B 的可衡量的改进，但只有在最极端的情况下才会产生“真正”的差异。除非确实有必要，否则为什么要冒“风险”呢？
人们问这个问题的根本原因始终是性能。
外键为优化器提供了更多可供使用的信息，并且它可能会产生更好的执行计划。这并不是说某个特定的查询在启用约束的情况下会快%%，而更像是您有效地消除了由于错误的执行计划而导致的整类问题。您还可以使优化器以在没有约束的情况下不可能实现的方式重写查询（例如连接消除）。
从这里开始，我想开始一个神话：引用完整性总是会提高数据库的性能。我相当有信心，如果 100 个人在设计他们的数据库时进行了全面的完整性检查，那么实际上只有不到 5 个人会因为性能原因考虑花 1 秒的时间来禁用它们。在这 5 个人中，将有接近 0 人发现他们需要禁用 100% 的约束。
Not using referential integrity in a database is like not using seatbelts in cars. It will provide you with measurable improvements in taking you from A->B, but it will make "real" difference only in the most extreme cases. Why take the "risk" unless you really have to?
The underlaying reason people ask this question is always performance.
Foreign keys give the optimizer much more information to work with, and it will potentially produce better execution plans. It's not like a specific query will be % percent faster with enabled constraints, it's more like you effectively eliminate entire classes of problems due to bad execution plans. You also enable the optimizer to rewrite queries in ways that just isn't possible without the constraints (join elimination for example).
Starting right here, I would like to start a myth that referential integrity always increases performance in databases. I'm fairly confident that if 100 people designed their databases with full integrity checking, less than 5 people will actually have to consider spend a whopping 1 second to disable them for performance reasons. Out of those 5 people, there will be close to 0 people who find that they need to disable 100% of the constraints.
你说
您正在寻找的表达是，“我百分百确定每个程序员和每个数据库管理员都会手动完美地保留数据完整性，无论什么应用程序接触该数据库，无论数据库变得多么复杂，从现在到那时它已经退役了。”
You said
The expression you were looking for is, "I'm 100% certain that every programmer and every database administrator will manually preserve data integrity perfectly no matter what application touches this database, no matter how complex the database becomes, from now until the time it's decommissioned."
您不必使用它们，但为什么不呢？
他们随时提供帮助。从通过级联更新和级联删除让生活变得更轻松，到保证不违反约束。
也许应用程序遵守这些约束，但是明确指定它们不是很有用吗？您可以记录它们，或者可以将它们放入数据库中，大多数程序员希望在数据库中找到他们应该遵守的约束（我认为这是一个更好的主意！）。
最后，如果您需要将不通过前端的数据导入到此数据库中，您可能会意外导入违反约束并破坏应用程序的数据。
我绝对不建议跳过数据库中的关系
You don't have to use them but why wouldn't you?
They are there to help. From making life easier with cascade updates and cascade deletes, to guaranteeing that constraints aren't violated.
Maybe the application honors the constraints, but isn't it useful to have them clearly specified? You could document them, or you could put them in the database where most programmers expect to find constraints they are supposed to conform to (a better idea I think!).
Finally, if you ever need to import data into this database which doesn't go via the front-end, you may accidently import data which violates the constraints and breaks the application.
I'd definetly not recommend skipping the relationships in a database
使用报告生成器和数据分析工具时，外键使生活变得更加轻松。只需选择一个表格，选中
包括相关表格框，然后BAM！就可以生成报告了。好吧，这并不容易，但在这方面他们确实节省了时间。Foreign Keys make life so much easier when using report builders and data analysis tools. Just select one table, check the
include related tablesbox and BAM! you've got you're report built. Ok Ok, it's not that easy, but they certianly save time in that respect.使用约束而不是应用程序逻辑来强制完整性，因为在一个地方（数据库）而不是在每个应用程序中维护约束通常更容易、更便宜且更可靠。
我从您的评论中了解到，您提出这个问题的动机是您认为省略键可能会使开发过程中的数据库设计更容易发展。根据我的经验，你的想法是错误的。我发现在开发的早期阶段更多限制实际上更好。如果有疑问，请创建约束，因为稍后删除约束比创建约束要容易得多。删除约束往往会比添加约束破坏更少的东西，并且通常需要更少的测试和更少的代码更改来实现。
Use constraints rather than application logic to enforce integrity because it is generally easier, cheaper and more reliable to maintain constraints in one place (the database) rather than in every application.
I understand from one of your comments that your motivation for asking the question is that you think leaving out the keys may make it easier to evolve the database design during development. In my experience you are wrong about that. I find that it's actually better to be more restrictive with constraints in the early stages of development. If in doubt, create the constraint because it's much easier to remove constraints later than it is to create them. Removing a constraint will tend to break fewer things than adding one and generally requires less testing and fewer code changes to achieve.
另一点需要指出的是，当您废弃当前的用户界面并使用带有闪亮新工具的新用户界面时，您不会失去引用完整性，因为新开发人员不知道什么应该与什么相关。数据库的使用时间通常比用户界面的使用时间长得多。它们还经常被多个应用程序接口使用，然后您就会遇到不同的接口试图强制执行不同的完整性规则的问题。
我还要指出的是，我曾经有机会查看过数百个数据库中的数据，而且还没有找到一个在没有设置 FK 的情况下拥有良好数据的数据库。这些不良数据使报告变得复杂，使与需要或提供数据的客户和其他第三方供应商之间的进出口变得复杂。如果不良数据属于财务领域，它也可能会产生法律和会计影响。我什至记得有一次，该公司有数千条不良库存记录，其中存储的实际产品不再可识别（也无法识别位置），这也给定义财务报告所需的库存价值带来了问题。从不知道您手头有哪些零件的角度来看，这不仅是不好的，而且它使人们能够通过简单地从零件表中删除零件编号来窃取零件而不会被抓住（这个特定的地方也没有适当的审核） .)。
Another point to make is that when you scrap your current user interface and use a new one with shiny new tools, you won't lose your referential integrity because the new devs have no idea what should be related to what. Databases are generally in use much much longer than user interfaces. They are also often used by more than one application interface and then you have the problem of different interfaces trying to enforce different integrity rules.
I will also point out that I have had occasion to look at the data in, quite literally, hundreds of databases and have not found one yet that has good data if they didn't set up FKs. This bad data complicates reporting, it complicates imports and exports to and from clients and other third party vendors who need or provide the data. And if the bad data is in a financial area, it could also have legal and accounting implications. I can even remember one time the company had thousands of bad inventory records where the actual product that was stored was no longer identifiable (nor the location) which also created issues with defining the value of the inventory necessary for financial reporting. This is not only bad from a perspective of not knowing what parts you have on hand, but it enables people to steal parts without being caught simply by deleting the part number from the part table (this particular place didn't have auditing in place either.).
上面的人已经给出了一些很好的答案。然而，我没有看到提及的重要一点是，外键使实体关系图 (ERD) 更容易生成并且更有意义。如果没有 FK，您要么需要在 ERD 上手动描述 FK 关系（这对您来说很痛苦），要么根本不需要（对其他人来说很痛苦，甚至可能对您自己来说，一旦您对隐含 FK 关系的记忆开始随着时间的推移而消失）。通过显式定义 FK，大多数从数据库对象定义自动生成 ERD 的工具将自动检测和描述 FK 关系。
Folks have offered up some good answers above. However, one important point I didn't see mentioned is that foreign keys make your entity relationship diagrams (ERDs) easier to generate and much more meaningful. Without FKs, you either need to depict the FK relationships on your ERD manually (painful for you) or not at all (painful for others, and perhaps even for yourself once your memory of the implied FK relationships starts to fade over time). With FKs explicitly defined, most tools that automatically generate ERDs from database object definitions will automatically detect and depict the FK relationships.
也许问题应该是“孤儿记录有多糟糕？”。在许多情况下，孤立的记录实际上不会造成任何伤害。是的，这些记录可能会一直存在到时间的尽头，但这到底有多糟糕呢？级联更新或删除很少是有用的功能。参照完整性听起来不错，但我认为它并不像我们想象的那么重要。 FK 的最大好处是他们提供的文档。根据我的经验，FK 的引用完整性带来的麻烦远大于其价值。
Perhaps the question should be "How bad are orphan records?". In many cases orphaned records aren't really going to hurt anything. Yes these records may persist until the end of time but how bad is this really? Cascading updates or deletes are rarely useful features. Referential integrity sounds nice but I think is not as important as we have been lead to believe. The biggest benefit to FK's is the documentation they provide. In my experience FK's for referential integrity are way more trouble than they are worth.
我今天也有同样的问题，发现很多文章都在谈论为什么不必在线使用外键。但到目前为止，这里 11 个答案中有 10 个说你应该有 FK。
我不是数据库专家，只是想分享一些我在网上找到的关于何时以及为何没有 FK 的观点：
来自 没有外键约束的 9 个原因：
在 GitHub，我们无论何时何地都不会使用外键。
注：我没有任何意见。只是分享一些在线文章来为当前大多数文章提供不同的答案。
I am having the same question today, and found many articles talking about why you don't have to use foreign keys online. But so far, 10 of 11 answers here say you should have FKs.
I am not a db expert and just want to share some points I found online about when and why you don't have FKs:
Some points from 9 reasons why there are no foreign keys constraints:
Some points from At GitHub we do not use foreign keys, ever, anywhere.
Note: I don't have any opinions. Just sharing some online articles to provide a different answer to most of the current ones.