What’s wrong with foreign keys?

by

I remember hearing Joel Spolsky mention in podcast 014 that he’d barely ever used a foreign key (if I remember correctly). However, to me they seem pretty vital to avoid duplication and subsequent data integrity problems throughout your database.

Do people have some solid reasons as to why (to avoid a discussion in lines with Stack Overflow principles)?

Edit: “I’ve yet to have a reason to create a foreign key, so this might be my first reason to actually set up one.”

38 Answers
38

Reasons to use Foreign Keys:

  • you won’t get Orphaned Rows
  • you can get nice “on delete cascade” behavior, automatically cleaning up tables
  • knowing about the relationships between tables in the database helps the Optimizer plan your queries for most efficient execution, since it is able to get better estimates on join cardinality.
  • FKs give a pretty big hint on what statistics are most important to collect on the database, which in turn leads to better performance
  • they enable all kinds of auto-generated support — ORMs can generate themselves, visualization tools will be able to create nice schema layouts for you, etc.
  • someone new to the project will get into the flow of things faster since otherwise implicit relationships are explicitly documented

Reasons not to use Foreign Keys:

  • you are making the DB work extra on every CRUD operation because it has to check FK consistency. This can be a big cost if you have a lot of churn
  • by enforcing relationships, FKs specify an order in which you have to add/delete things, which can lead to refusal by the DB to do what you want. (Granted, in such cases, what you are trying to do is create an Orphaned Row, and that’s not usually a good thing). This is especially painful when you are doing large batch updates, and you load up one table before another, with the second table creating consistent state (but should you be doing that sort of thing if there is a possibility that the second load fails and your database is now inconsistent?).
  • sometimes you know beforehand your data is going to be dirty, you accept that, and you want the DB to accept it
  • you are just being lazy 🙂

I think (I am not certain!) that most established databases provide a way to specify a foreign key that is not enforced, and is simply a bit of metadata. Since non-enforcement wipes out every reason not to use FKs, you should probably go that route if any of the reasons in the second section apply.

Leave a Comment