What is the advantage of defining a foreign key when working with an MVC framework that handles the relation?
在处理处理关系的MVC框架时，定义外键有什么好处？
I'm using a relational database with a framework that allows model definitions with relations. Because the foreign keys are defined through the models, it seems like foreign keys are redundant. When it comes to managing the database of an application in development, editing/deleting tables that are using foreign keys is a hassle.
我正在使用一个关系数据库，其框架允许模型定义与关系。因为外键是通过模型定义的，所以外键似乎是多余的。在开发中管理应用程序的数据库时，编辑/删除使用外键的表是一件麻烦事。
Is there any advantage to using foreign keys that I'm forgoing by dropping the use of them altogether?
通过完全放弃使用外键，使用外键是否有任何优势？
5 个解决方案
#1
33
Foreign keys with constraints(in some DB engines) give you data integrity on the low level(level of database). It means you can't physically create a record that doesn't fulfill relation. It's just a way to be more safe.
带有约束的外键（在某些数据库引擎中）可以在低级别（数据库级别）上提供数据完整性。这意味着您无法在物理上创建不满足关系的记录。这只是一种更安全的方式。
#2
13
It gives you data integrity that's enforced at the database level. This helps guard against possibly error in application logic that might cause invalid data.
它为您提供在数据库级别强制执行的数据完整性。这有助于防止可能导致无效数据的应用程序逻辑中的错误。
If any data manipulation is ever done directly in SQL that bypasses your application logic, it also guards against bad data that breaks those constraints.
如果任何数据操作直接在SQL中绕过您的应用程序逻辑，它还可以防止破坏这些约束的坏数据。
An additional side-benefit is that it allows tools to automatically generating database diagrams with relationships inferred from the schema itself. Now in theory all the diagramming should be done before the database is created, but as the database evolves beyond its initial incarnation these diagrams often aren't kept up to date, and the ability to generate a diagram from an existing database is helpful both for reviewing, as well as for explaining the structure to new developers joining a project.
另一个好处是它允许工具使用从模式本身推断出的关系自动生成数据库图表。现在理论上所有的图表都应该在创建数据库之前完成，但是随着数据库的发展超出其初始化身，这些图表通常不会保持最新，并且从现有数据库生成图表的能力对于审查，以及向加入项目的新开发人员解释结构。
It might be a helpful to disable FKs while the database structure is still in flux, but they're good safeguard to have when the schema is more stabilized.
在数据库结构仍然不稳定的情况下禁用FK可能会有所帮助，但是当架构更稳定时，它们是很好的保障。
#3
11
A foreign key guarantees a matching record exists in a foreign table. Imagine a table called Books that has a FK constraint on a table called Authors. Every book is guaranteed to have an Author.
外键保证外表中存在匹配记录。想象一下名为Books的表，它在名为Authors的表上具有FK约束。每本书都保证有作者。
Now, you can do a query such as:
现在，您可以执行以下查询：
SELECT B.Title, A.Name FROM Books B
INNER JOIN Authors A ON B.AuthorId = A.AuthorId;
Without the FK constraint, a missing Author row would cause the entire Book row to be dropped, resulting in missing books in your dataset.
如果没有FK约束，缺少的Author行将导致整个Book行被删除，从而导致数据集中的书籍丢失。
Also, with the FK constraint, attempting to delete an author that was referred to by at least one Book would result in an error, rather than corrupting your database.
此外，使用FK约束，尝试删除至少由一本书引用的作者将导致错误，而不是破坏您的数据库。
#4
5
Whilst they may be a pain when manipulating development/test data, they have saved me a lot of hassle in production.
虽然在操作开发/测试数据时它们可能会很痛苦，但它们在生产中为我节省了很多麻烦。
Think of them as a way to maintain data integrity, especially as a safeguard against orphaned records.
将它们视为维护数据完整性的一种方式，尤其是作为防止孤立记录的安全措施。
For example, if you had a database relating many PhoneNumber records to a Person, what happens to PhoneNumber records when the Person record is deleted for whatever reason? They will still exist in the database, but the ID of the Person they relate to will no longer exist in the relevant Person table and you have orphaned records.
例如，如果您有一个数据库将许多PhoneNumber记录与Person相关联，那么当Person记录因任何原因被删除时，PhoneNumber记录会发生什么？它们仍然存在于数据库中，但它们相关的Person的ID将不再存在于相关的Person表中，并且您有孤立的记录。
Yes, you could write a trigger to delete the PhoneNumber whenever a Person gets removed, but this could get messy if you accidentally delete a Person and need to rollback.
是的，每当一个Person被删除时，你可以编写一个触发器来删除PhoneNumber，但如果你不小心删除了一个Person并需要回滚，这可能会变得混乱。
Yes, you may remember to get rid of the PhoneNumber records manually, but what about other developers or methods you write 9 months down the line?
是的，您可能还记得手动删除PhoneNumber记录，但是您在9个月后编写的其他开发人员或方法呢？
By creating a Foreign Key that ensures any PhoneNumber is related to an existing Person, you both insure against destroying this relationship and also add 'clues' as to the intended data structure.
通过创建确保任何PhoneNumber与现有Person相关的外键，您既可以防止破坏这种关系，也可以为预期的数据结构添加“线索”。
#5
2
The main benefits are data integrity and cascading deletes. You can also get a performance gain when they're defined and those fields are properly indexed. For example, you wouldn't be able to create a phone number that didn't belong to a contact, or when you delete the contact you can set it to automatically delete all of their phone numbers. Yes, you can make those connections in your UI or middle tier, but you'll still end up with orphans if someone runs an update directly against the server using SQL rather than your UI. The "hassle" part is just forcing you to consider those connections before you make a bulk change. FKs have saved my bacon many times.
主要好处是数据完整性和级联删除。在定义这些字段并正确编制索引后，您还可以获得性能提升。例如，您将无法创建不属于某个联系人的电话号码，或者当您删除该联系人时，您可以将其设置为自动删除所有电话号码。是的，你可以在你的UI或中间层建立这些连接，但如果有人使用SQL而不是你的UI直接针对服务器运行更新，你仍然会遇到孤儿。 “麻烦”部分只是强迫您在进行批量更改之前考虑这些连接。 FK多次拯救了我的培根。
#1
33
Foreign keys with constraints(in some DB engines) give you data integrity on the low level(level of database). It means you can't physically create a record that doesn't fulfill relation. It's just a way to be more safe.
带有约束的外键（在某些数据库引擎中）可以在低级别（数据库级别）上提供数据完整性。这意味着您无法在物理上创建不满足关系的记录。这只是一种更安全的方式。
#2
13
It gives you data integrity that's enforced at the database level. This helps guard against possibly error in application logic that might cause invalid data.
它为您提供在数据库级别强制执行的数据完整性。这有助于防止可能导致无效数据的应用程序逻辑中的错误。
If any data manipulation is ever done directly in SQL that bypasses your application logic, it also guards against bad data that breaks those constraints.
如果任何数据操作直接在SQL中绕过您的应用程序逻辑，它还可以防止破坏这些约束的坏数据。
An additional side-benefit is that it allows tools to automatically generating database diagrams with relationships inferred from the schema itself. Now in theory all the diagramming should be done before the database is created, but as the database evolves beyond its initial incarnation these diagrams often aren't kept up to date, and the ability to generate a diagram from an existing database is helpful both for reviewing, as well as for explaining the structure to new developers joining a project.
另一个好处是它允许工具使用从模式本身推断出的关系自动生成数据库图表。现在理论上所有的图表都应该在创建数据库之前完成，但是随着数据库的发展超出其初始化身，这些图表通常不会保持最新，并且从现有数据库生成图表的能力对于审查，以及向加入项目的新开发人员解释结构。
It might be a helpful to disable FKs while the database structure is still in flux, but they're good safeguard to have when the schema is more stabilized.
在数据库结构仍然不稳定的情况下禁用FK可能会有所帮助，但是当架构更稳定时，它们是很好的保障。
#3
11
A foreign key guarantees a matching record exists in a foreign table. Imagine a table called Books that has a FK constraint on a table called Authors. Every book is guaranteed to have an Author.
外键保证外表中存在匹配记录。想象一下名为Books的表，它在名为Authors的表上具有FK约束。每本书都保证有作者。
Now, you can do a query such as:
现在，您可以执行以下查询：
SELECT B.Title, A.Name FROM Books B
INNER JOIN Authors A ON B.AuthorId = A.AuthorId;
Without the FK constraint, a missing Author row would cause the entire Book row to be dropped, resulting in missing books in your dataset.
如果没有FK约束，缺少的Author行将导致整个Book行被删除，从而导致数据集中的书籍丢失。
Also, with the FK constraint, attempting to delete an author that was referred to by at least one Book would result in an error, rather than corrupting your database.
此外，使用FK约束，尝试删除至少由一本书引用的作者将导致错误，而不是破坏您的数据库。
#4
5
Whilst they may be a pain when manipulating development/test data, they have saved me a lot of hassle in production.
虽然在操作开发/测试数据时它们可能会很痛苦，但它们在生产中为我节省了很多麻烦。
Think of them as a way to maintain data integrity, especially as a safeguard against orphaned records.
将它们视为维护数据完整性的一种方式，尤其是作为防止孤立记录的安全措施。
For example, if you had a database relating many PhoneNumber records to a Person, what happens to PhoneNumber records when the Person record is deleted for whatever reason? They will still exist in the database, but the ID of the Person they relate to will no longer exist in the relevant Person table and you have orphaned records.
例如，如果您有一个数据库将许多PhoneNumber记录与Person相关联，那么当Person记录因任何原因被删除时，PhoneNumber记录会发生什么？它们仍然存在于数据库中，但它们相关的Person的ID将不再存在于相关的Person表中，并且您有孤立的记录。
Yes, you could write a trigger to delete the PhoneNumber whenever a Person gets removed, but this could get messy if you accidentally delete a Person and need to rollback.
是的，每当一个Person被删除时，你可以编写一个触发器来删除PhoneNumber，但如果你不小心删除了一个Person并需要回滚，这可能会变得混乱。
Yes, you may remember to get rid of the PhoneNumber records manually, but what about other developers or methods you write 9 months down the line?
是的，您可能还记得手动删除PhoneNumber记录，但是您在9个月后编写的其他开发人员或方法呢？
By creating a Foreign Key that ensures any PhoneNumber is related to an existing Person, you both insure against destroying this relationship and also add 'clues' as to the intended data structure.
通过创建确保任何PhoneNumber与现有Person相关的外键，您既可以防止破坏这种关系，也可以为预期的数据结构添加“线索”。
#5
2
The main benefits are data integrity and cascading deletes. You can also get a performance gain when they're defined and those fields are properly indexed. For example, you wouldn't be able to create a phone number that didn't belong to a contact, or when you delete the contact you can set it to automatically delete all of their phone numbers. Yes, you can make those connections in your UI or middle tier, but you'll still end up with orphans if someone runs an update directly against the server using SQL rather than your UI. The "hassle" part is just forcing you to consider those connections before you make a bulk change. FKs have saved my bacon many times.
主要好处是数据完整性和级联删除。在定义这些字段并正确编制索引后，您还可以获得性能提升。例如，您将无法创建不属于某个联系人的电话号码，或者当您删除该联系人时，您可以将其设置为自动删除所有电话号码。是的，你可以在你的UI或中间层建立这些连接，但如果有人使用SQL而不是你的UI直接针对服务器运行更新，你仍然会遇到孤儿。 “麻烦”部分只是强迫您在进行批量更改之前考虑这些连接。 FK多次拯救了我的培根。