database 在数据库设计中真的需要外键吗？

Are foreign keys really necessary in a database design?

提问by Niyaz

As far as I know, foreign keys (FK) are used to aid the programmer to manipulate data in the correct way. Suppose a programmer is actually doing this in the right manner already, then do we really need the concept of foreign keys?

据我所知，外键 (FK) 用于帮助程序员以正确的方式操作数据。假设程序员实际上已经以正确的方式这样做了，那么我们真的需要外键的概念吗？

Are there any other uses for foreign keys? Am I missing something here?

外键还有其他用途吗？我在这里错过了什么吗？

采纳答案by John Topley

Foreign keys help enforce referential integrity at the data level. They also improve performance because they're normally indexed by default.

外键有助于在数据级别强制执行参照完整性。它们还提高了性能，因为它们通常默认被索引。

回答by Greg Hewgill

Foreign keys can also help the programmer write less code using things like ON DELETE CASCADE. This means that if you have one table containing users and another containing orders or something, then deleting a user could automatically delete all orders that point to that user.

外键还可以帮助程序员使用诸如ON DELETE CASCADE. 这意味着如果您有一个包含用户的表和另一个包含订单或其他内容的表，那么删除用户可以自动删除指向该用户的所有订单。

回答by Eric Z Beard

I can't imagine designing a database without foreign keys. Without them, eventually you are bound to make a mistake and corrupt the integrity of your data.

我无法想象设计一个没有外键的数据库。没有它们，最终您肯定会犯错误并破坏数据的完整性。

They are not required, strictly speaking, but the benefits are huge.

严格来说，它们不是必需的，但好处是巨大的。

I'm fairly certain that FogBugzdoes not have foreign key constraints in the database. I would be interested to hear how the Fog Creek Softwareteam structures their code to guarantee that they will never introduce an inconsistency.

我相当肯定FogBugz在数据库中没有外键约束。我很想知道Fog Creek Software团队如何构建他们的代码以保证他们永远不会引入不一致。

回答by Guy

A database schema without FK constraints is like driving without a seat belt.

没有 FK 约束的数据库模式就像没有安全带开车一样。

One day, you'll regret it. Not spending that little extra time on the design fundamentals and data integrity is a sure fire way of assuring headaches later.

总有一天，你会后悔的。不在设计基础和数据完整性上花费很少的额外时间是确保以后头痛的可靠方法。

Would you accept code in your application that was that sloppy? That directly accessed the member objects and modified the data structures directly.

你会接受你的应用程序中那么草率的代码吗？即直接访问成员对象并直接修改数据结构。

Why do you think this has been made hard and even unacceptablewithin modern languages?

为什么你认为这在现代语言中变得困难甚至不可接受？

回答by csmba

Yes.

是的。

1. They keep you honest
2. They keep new developers honest
3. You can do ON DELETE CASCADE
4. They help you to generate nice diagrams that self explain the links between tables

1. 他们让你诚实
2. 他们让新开发人员保持诚实
3. 你可以做 ON DELETE CASCADE
4. 它们可以帮助您生成精美的图表，这些图表可以自我解释表格之间的链接

回答by Peter Meyer

Personally, I am in favor of foreign keys because it formalizes the relationship between the tables. I realize that your question presupposes that the programmer is not introducing data that would violate referential integrity, but I have seen way too many instances where data referential integrity is violated, despite best intentions!

就我个人而言，我赞成使用外键，因为它使表之间的关系正式化。我意识到您的问题的前提是程序员没有引入会违反参照完整性的数据，但我已经看到太多违反数据参照完整性的实例，尽管有最好的意图！

Pre-foreign key constraints (aka declarative referential integrity or DRI) lots of time was spent implementing these relationships using triggers. The fact that we can formalize the relationship by a declarative constraint is very powerful.

前外键约束（又名声明性引用完整性或 DRI）花费了大量时间使用触发器来实现这些关系。我们可以通过声明性约束形式化关系这一事实非常强大。

@John - Other databases may automatically create indexes for foreign keys, but SQL Server does not. In SQL Server, foreign key relationships are only constraints. You must defined your index on foreign keys separately (which can be of benefit.)

@John - 其他数据库可能会自动为外键创建索引，但 SQL Server 不会。在 SQL Server 中，外键关系只是约束。您必须单独在外键上定义索引（这可能会有好处。）

Edit: I'd like to add that, IMO, the use of foreign keys in support of ON DELETE or ON UPDATE CASCADE is not necessarily a good thing. In practice, I have found that cascade on delete should be carefully considered based on the relationship of the data -- e.g. do you have a natural parent-child where this may be OK or is the related table a set of lookup values. Using cascaded updates implies you are allowing the primary key of one table to be modified. In that case, I have a general philosophical disagreement in that the primary key of a table should not change. Keys should be inherently constant.

编辑：我想补充一点，IMO，使用外键支持 ON DELETE 或 ON UPDATE CASCADE 不一定是一件好事。在实践中，我发现应该根据数据的关系仔细考虑删除级联 - 例如，您是否有一个自然的父子关系，这可能没问题，或者相关表是一组查找值。使用级联更新意味着您允许修改一个表的主键。在那种情况下，我有一个普遍的哲学分歧，即表的主键不应该改变。键应该是固有不变的。

回答by DrPizza

Suppose a programmer is actually doing this in the right manner already

假设一个程序员实际上已经以正确的方式这样做了

Making such a supposition seems to me to be an extremely bad idea; in general software is phenomenally buggy.

做出这样的假设在我看来是一个非常糟糕的主意。一般来说，软件是非常错误的。

And that's the point, really. Developers can't get things right, so ensuring the database can't be filled with bad data is a Good Thing.

这就是重点，真的。开发人员无法把事情做好，因此确保数据库不会被不良数据填充是一件好事。

Although in an ideal world, natural joins would use relationships (i.e. FK constraints) rather than matching column names. This would make FKs even more useful.

尽管在理想世界中，自然连接将使用关系（即 FK 约束）而不是匹配列名。这将使 FK 更加有用。

回答by samjudson

Without a foreign key how do you tell that two records in different tables are related?

如果没有外键，如何判断不同表中的两条记录是相关的？

I think what you are referring to is referential integrity, where the child record is not allowed to be created without an existing parent record etc. These are often known as foreign key constraints - but are not to be confused with the existence of foreign keys in the first place.

我认为你所指的是参照完整性，在没有现有父记录的情况下不允许创建子记录等。这些通常称为外键约束 - 但不要与外键的存在混淆第一名。

回答by Tundey

Is there a benefit to not having foreign keys? Unless you are using a crappy database, FKs aren't that hard to set up. So why would you have a policy of avoiding them? It's one thing to have a naming convention that says a column references another, it's another to know the database is actually verifying that relationship for you.

没有外键有好处吗？除非您使用蹩脚的数据库，否则设置 FK 并不难。那么为什么要制定避免它们的策略呢？拥有一个列引用另一个列的命名约定是一回事，知道数据库实际上正在为您验证该关系是另一回事。

回答by JacquesB

I suppose you are talking about foreign key constraints enforced by the database. You probably already are using foreign keys, you just haven't told the database about it.

我想您是在谈论由数据库强制执行的外键约束。您可能已经在使用外键，只是没有告诉数据库而已。

Suppose a programmer is actually doing this in the right manner already, then do we really need the concept of foreign keys?

假设程序员实际上已经以正确的方式这样做了，那么我们真的需要外键的概念吗？

Theoretically, no. However, there have never been a piece of software without bugs.

理论上，没有。然而，从来没有一款软件没有缺陷。

Bugs in application code are typically not that dangerous - you identify the bug and fix it, and after that the application runs smoothly again. But if a bug allows currupt data to enter the database, then you are stuck with it! It's very hard to recover from corrupt data in the database.

应用程序代码中的错误通常没有那么危险——您可以识别错误并修复它，之后应用程序将再次顺利运行。但是如果一个错误允许错误的数据进入数据库，那么你就被它卡住了！从数据库中损坏的数据中恢复是非常困难的。

Consider if a subtle bug in FogBugzallowed a corrupt foreign key to be written in the database. It might be easy to fix the bug and quickly push the fix to customers in a bugfix release. However, how should the corrupt data in dozens of databases be fixed? Correctcode might now suddenly break because the assumptions about the integrity of foreign keys dont hold anymore.

考虑FogBugz 中的一个细微错误是否允许将损坏的外键写入数据库。修复错误并在错误修复版本中快速将修复推送给客户可能很容易。但是，数十个数据库中的损坏数据应该如何修复？正确的代码现在可能会突然中断，因为关于外键完整性的假设不再成立。

In web applications you typically only have one program speaking to the database, so there is only one place where bugs can corrupt the data. In an enterprise application there might be several independent applications speaking to the same database (not to mention people working directly with the database shell). There is no way to be sure that all applications follow the same assumptions without bugs, always and forever.

在 Web 应用程序中，您通常只有一个程序与数据库对话，因此只有一处错误会破坏数据。在企业应用程序中，可能有多个独立的应用程序与同一个数据库对话（更不用说直接使用数据库 shell 的人了）。无法确保所有应用程序始终遵循相同的假设而没有错误。

If constraints are encoded in the database, then the worst that can happen with bugs is that the user is shown an ugly error message about some SQLconstraint not satisfied. This is muchprefereable to letting currupt data into your enterprise database, where it in turn will break all your applications or just lead to all kinds of wrong or misleading output.

如果在数据库中编码了约束，那么错误可能发生的最糟糕的情况是向用户显示有关某些SQL约束未满足的丑陋错误消息。这比让错误的数据进入您的企业数据库更可取，它反过来会破坏您的所有应用程序或导致各种错误或误导性的输出。

Oh, and foreign key constraints also improves performance because they are indexed by default. I can't think of any reason notto use foreign key constraints.

哦，外键约束也提高了性能，因为它们默认被索引。我想不出任何不使用外键约束的理由。