what are the advantages of defining a foreign key

问题:

What is the advantage of defining a foreign key when working with an MVC framework that handles the relation?

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?

回答1:

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:

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.

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.

回答3:

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.

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.

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.

回答4:

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.

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.

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?

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.

回答5:

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.