database 外键有什么问题？

What's wrong with foreign keys?

提问by ljs

I remember hearing Joel Spolskymention in podcast 014that 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.

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

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

人们是否有充分的理由来解释为什么（避免根据 Stack Overflow 原则进行讨论）？

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

编辑：“我还没有理由创建外键，所以这可能是我实际设置外键的第一个原因。”

回答by SquareCog

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
• 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

• 你不会得到孤立行
• 你可以得到很好的“删除级联”行为，自动清理表格
• 了解数据库中表之间的关系有助于优化器计划您的查询以最有效地执行，因为它能够更好地估计连接基数。
• FK 提供了一个非常重要的提示，说明哪些统计数据最重要的是要在数据库上收集，这反过来又会导致更好的性能
• 它们支持各种自动生成的支持——ORM 可以自己生成，可视化工具将能够为您创建漂亮的模式布局等。
• 项目的新成员将更快地进入事物的流程，因为其他隐式关系被明确记录

Reasons not to use Foreign Keys:

不使用外键的原因：

• you are making the DB work extra on every CRUDoperation 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 :-)

• 您正在使 DB 在每个CRUD操作上都额外工作，因为它必须检查 FK 一致性。如果你有很多流失，这可能是一个很大的成本
• 通过强制关系，FK 指定您必须添加/删除内容的顺序，这可能导致 DB 拒绝执行您想要的操作。（当然，在这种情况下，您要做的是创建一个孤立行，这通常不是一件好事）。当您进行大批量更新时，这尤其痛苦，并且您在另一个表之前加载一个表，第二个表创建一致的状态（但是如果第二次加载可能失败并且您的数据库现在不一致？）。
• 有时您事先知道您的数据会变脏，您接受这一点，并且您希望数据库接受它
• 你只是懒惰:-)

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.

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

回答by Ed Lucas

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 相关工作中没有获得过如此全面或积极的体验，那么您可能没有接触过此类信息。或者，也许您的过去包括沉浸在强烈反对数据库的环境中（例如，“那些 DBA 是白痴 - 我们很少，我们选择了很少的 java/c# 代码投掷者将挽救这一天”），在这种情况下，您可能会强烈反对一些傻瓜的神秘咿呀学语告诉你，如果你只是倾听，FK（以及它们可能暗示的约束）真的很重要。

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

大多数人在孩提时都被教导刷牙很重要。没有它你能熬过去吗？当然，但在某些地方，您可用的牙齿会比每顿饭后刷牙时可用的牙齿少。如果妈妈和爸爸有足够的责任来涵盖数据库设计和口腔卫生，我们就不会进行这种对话。:-)

回答by AlexCuse

I'm sure there are plenty of applications where you can get away with it, but it's 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 relationaldatabase then it seems you ought to have some relationshipsdefined 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 ;)

如果您使用的是关系数据库，那么您似乎应该在其中定义一些关系。不幸的是，这种态度（您不需要外键）似乎被许多应用程序开发人员所接受，他们不想被数据完整性等愚蠢的事情所困扰（但需要这样做，因为他们的公司没有专门的数据库开发人员）。通常在由这些类型组合在一起的数据库中，您很幸运拥有主键 ;)

回答by Galwegian

Foreign keys are essentialto any relational database model.

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

回答by Ant

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 框的 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?

如果您的数据很重要，那么是的，使用外键，创建一套存储过程来与数据交互，并制作最坚固的数据库。如果您的数据不重要，为什么要从一开始就创建数据库？

回答by Nathan Long

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

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

---

---

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: accountsand transactions.

假设您正在使用外键。您正在编写一个自动化测试，说明“当我更新金融账户时，它应该保存交易记录。” 在这个测试中，您只关心两个表：accounts和transactions。

However, accountshas a foreign key to contracts, and contractshas a fk to clients, and clientshas a fk to cities, and citieshas a fk to states.

然而，accounts有一个外键contracts，并contracts有一个FK来clients，并clients有一个FK来cities，并cities有一个FK来states。

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 至少支持这个。

回答by Powerlord

"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.
• 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 DELETE RESTRICT- 防止其他表中在该列中具有键的任何行被删除。这就是 Ken Ray 上面所描述的。
• ON DELETE CASCADE- 如果另一个表中的一行被删除，则删除该表中引用它的所有行。
• ON DELETE SET DEFAULT- 如果另一个表中的一行被删除，将引用它的任何外键设置为该列的默认值。
• ON DELETE SET NULL- 如果另一个表中的一行被删除，则将此表中引用它的任何外键设置为空。
• ON DELETE NO ACTION- 这个外键只是表明它是外键；即用于 OR 映射器。

These same actions also apply to ON UPDATE.

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

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

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

回答by Ed Guiness

@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 guaranteedto be at least consistent, in favour of so called "software enforcement" which is a weak preventative measure at best.

为什么哦，为什么你会忽略声明性引用完整性，其中数据可以保证至少是一致的，而支持所谓的“软件执行”，它充其量只是一种弱预防措施。

回答by Kent Fredric

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.

此外，相反，当您需要删除某些东西时，如果设计不当，约束可能会导致各种阻止您的锁定。

回答by Matt Rogish

There are no goodreasons notto use them... unless orphaned rows aren't a big deal to you I guess.

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