SQL 我应该使用外键吗？

Should I use foreign keys?

提问by

Possible Duplicate:
What's wrong with foreign keys?

可能的重复：
外键有什么问题？

I use MS Sql Server with a large database about 4 GB data.

我使用带有大约 4 GB 数据的大型数据库的 MS Sql Server。

I search around the web why I should use foreign keys. by now I only indexed the keys used to join tables. Performance is all fine, dataintegrety is no problem.

我在网上搜索为什么我应该使用外键。现在我只索引了用于连接表的键。性能没问题，数据完整性没问题。

Should I use foreign keys? Will I get even more performance with foreign keys?

我应该使用外键吗？使用外键我会获得更高的性能吗？

回答by Dana the Sane

Foreign key's don't actually improve performance, in fact they incur a small performance penalty on all write operations, to ensure that the constraint is followed.

外键实际上并没有提高性能，实际上它们对所有写操作都会造成很小的性能损失，以确保遵循约束。

The reason why you want to use these is to prevent destructive write operations. If you don't have them, buggy code, or a bad sql statement can remove rows that are expected to be there.

之所以要使用这些，是为了防止破坏性的写入操作。如果您没有它们，错误代码或错误的 sql 语句可能会删除预期存在的行。

回答by Tom H

Integrity may not be a problem today, but that's the exact attitude that makes it a problem tomorrow or two weeks from now.

诚信今天可能不是问题，但正是这种态度在明天或两周后使它成为问题。

回答by dkretz

A foreign key is primarily a tool for enforcing database integrity, which is unrelated to speed of execution.

外键主要是用于强制执行数据库完整性的工具，与执行速度无关。

If you have already optimized your index design, then you probably have these indexes already installed, at least as non-unique indexes. So I wouldn't expect any performance change just from installing foreign keys (whicb don't even necessarily involve an index.)

如果您已经优化了索引设计，那么您可能已经安装了这些索引，至少作为非唯一索引。所以我不希望安装外键有任何性能变化（甚至不一定涉及索引。）

I'd be a little suspicious of your complacency about the optimization of your design, though, if you don't already have this concept nailed.

不过，如果您还没有确定这个概念，我会有点怀疑您对设计优化的自满。

Read the documentation for Foreign Keys with the goal of understanding what they do to enforce integrity (it's worth knowing about in any case.) Then see if that doesn't answer your question more completely.

阅读有关外键的文档，目的是了解它们如何强制执行完整性（在任何情况下都值得了解）。然后看看这是否不能更完整地回答您的问题。

回答by Mr Bumbles

In MySQL you can disable Foreign Key's with SET FOREIGN_KEY_CHECKS=0

在 MySQL 中，您可以禁用外键 SET FOREIGN_KEY_CHECKS=0

回答by cdonner

What was not mentioned in the older question that SquareCog linked to earlier - yes, foreign key constraints can be a pain when doing data cleanup, batch updates, test data generation, or any type of operation where you bypass the normal sequence of things. But - you can always drop your foreign key constraints before you do something like this, and then recreate them again later (if you have your database objects scripted properly, this is hardly any extra work).

在 SquareCog 之前链接到的旧问题中没有提到什么 - 是的，外键约束在进行数据清理、批量更新、测试数据生成或任何类型的绕过正常顺序的操作时可能会很痛苦。但是 - 您总是可以在执行此类操作之前删除外键约束，然后稍后再次重新创建它们（如果您的数据库对象脚本正确，这几乎不需要任何额外的工作）。

I used to be lazy, but have come around to depending on foreign key constraints. There are still situations where you can't have them - like in cross-database relationships.

我曾经很懒惰，但已经习惯于依赖外键约束。仍然存在您无法拥有它们的情况 - 例如在跨数据库关系中。

回答by Aussie Craig

There is one feature/constraint which Foreign Keys bring to your system, which has not been mentioned so far. That is commit/transaction logic (that's what I call it anyway). With Foreign Keys enabled, all of the rows for an update, in all the tables affected need to be there for the commit to work (not throw a SQL error that the Foreign Key Constraints have been violated).

外键为您的系统带来了一项功能/约束，目前尚未提及。那是提交/事务逻辑（无论如何我都这么称呼它）。启用外键后，所有受影响的表中的所有更新行都需要存在才能使提交工作（不会抛出违反外键约束的 SQL 错误）。

If you have a body of code, which works and "plays fast and loose", with commits/transactions. Then you could be in for some remediation, to get things working with FK's in the schema.

如果你有一个代码体，它可以工作并且“快速和松散地播放”，带有提交/事务。然后您可以进行一些补救，以使架构中的 FK 能够正常工作。

Also, Oracle at least, lets you disable constraints(not just drop/remove). So you can switch them on/off easily. Handy, when you want to do some bulk operations either with out the overhead of the constraints, or to do some "surgery" on the data which has intermediate states which would fail the constraints.

此外，Oracle 至少可以让您禁用约束（不仅仅是删除/删除）。因此，您可以轻松地打开/关闭它们。方便，当您想要在没有约束开销的情况下执行一些批量操作时，或者对具有会使约束失败的中间状态的数据进行一些“手术”时。

回答by Milhous

Foreign keys also help to keep your database clean, as you can have the database do a cascading drop.

外键还有助于保持数据库清洁，因为您可以让数据库执行级联删除。

回答by Itay Moav -Malimovka

Foreign keys make data integrity better, performance, somewhat slower when deleting/inserting/updating.
In my last company we decided to keep integrity/connections in the BL, as it makes changes in the BL simpler (think hundreds of millions of records). If you have a small app, I see no reason why not do it in the data layer (db)

外键使数据完整性更好，性能，删除/插入/更新时稍慢。
在我上一家公司，我们决定在 BL 中保持完整性/连接，因为它使 BL 中的更改更简单（想想数亿条记录）。如果您有一个小应用程序，我认为没有理由不在数据层 (db) 中执行此操作