Foreign key for whom?!?

posted in: Design, SQL, Standards | 0

We recently had to redesign a database we have been using for quite some time. During the redesign process we noticed that the tables who were supposed to have relations in the form of foreign keys didn’t. Turns out that they were never setup.

This probed the question…”well, why or why not use primary keys?

So here is the for whom part:

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 🙂

*Note: This information is a direct copy of a comment on a stack-overflow post, the post can be viewed here: http://stackoverflow.com/questions/83147/whats-wrong-with-foreign-keys

Leave a Reply