39

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?

Daniel
  • 34,125
  • 17
  • 102
  • 150
  • 1
    Foreign key relationships enforce data integrity at a data layer thereby reducing bad data going into the system; no, not really. For example if you had a person table with a FK relationship to Gender Table a developer could insert a record into person with a gender of 'whocares'. Now, you may not care about this but if you build logic off valid entries and only expect male/female , then well... you'll have a problem. If you ever want to reverse engineer the DB to get a ERD, then you'd have problems as well. and scaffolding and other technologies relay on this level of design... – xQbert Apr 16 '12 at 23:38
  • 1
    Highly related: [Why are foreign keys more used in theory than in practice?](http://stackoverflow.com/questions/1876013/why-are-foreign-keys-more-used-in-theory-than-in-practice) –  Apr 16 '12 at 23:38
  • 2
    We had a similar scenario in a place I used to work: one app had no foreign keys because the ORM handled all such matters etc. One day a very important client discovered significant data loss that was eventually traced back to a bug in the ORM. Very damaging to the relationship, blaming the ORM developer doesn't help. Foreign keys were added very soon after! – onedaywhen Apr 17 '12 at 10:43
  • 1
    Why in the world did someone downvote this question and every answer? – Mike Christensen May 04 '17 at 04:13

5 Answers5

45

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.

shark555
  • 2,562
  • 1
  • 20
  • 10
  • 6
    Another advantage is cascading DELETE. For instance let's say you have a `User` table, and a `Class` table. Instead of rewriting all the User's data in `Class`, you just reference the id of the user. But what if that user is deleted? If you don't have cascading delete turned on, the row in the Class table points to a non-existent User. What if that User id is replaced by a completely different User? That's when stuff gets really bad. Cascading Delete will delete any records associated with that user. This may not seem important, but if `User` is connected to a lot of tables, this helps a lot. – Travis Heeter Jul 21 '15 at 14:10
  • 1
    Does it have any cost for us? – Ahmad Sep 10 '15 at 05:45
17

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.

Davy8
  • 30,868
  • 25
  • 115
  • 173
16

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.

Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
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.

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.

Widor
  • 13,003
  • 7
  • 42
  • 64
3

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.

Russell Fox
  • 5,273
  • 1
  • 24
  • 28