Debating Foreign Keys

Have you noticed that the dicussion whether foreign keys should be used in the database often leads to rather extreme positions being taken? Here are my thoughts on a recently published paper that brings forward some arguments why to question the use of foreign keys.

The author of the forecited paper, Marcel Kratochvil, has created a Foreign Key Discussion Blog where you can download his paper and leave comments.

Some of the key points presented in the paper are as follows:

  • Data integrity is different from foreign keys and foreign keys don’t assure data integrity
  • Foreign keys do not work with multimedia data
  • Normalization and foreign keys were introduced to avoid data redundancy and save disk space, which is no longer necessary
  • Foreign keys make sense in a logical data model (only)
  • Doing maintenance and reorgs with foreign keys is a real pain

I myself advance the view that foreign keys in general are a good thing. In my opinion the points made in this paper are not exactly moderate if you are a foreign key follower (like me). On the other hand it might be just what the typical objector always knew (and wasn’t afraid to emphasize).

Nevertheless I think there is a valid point in the main conclusion of the paper: always question why you do something and be prepared to pursue new directions when necessary.

But I don’t believe we have reached that point where better concepts are available and we could forget about foreign keys. Data integrity is an important issue (applications come and go but the data is there to stay) and just because it is not perfect doesn’t mean we should forget about it. To me that would be like abolishing safety belts just because they won’t keep you safe in every imaginable accident.

And the idea behind normalization is not to save disk space but to avoid DML anomalies. In my opinion there is nothing wrong with having controlled redundancy. But that would be part of the physical model only and doesn’t belong in the logical model. And I certainly prefer to have it read-only in the implementation.

Conclusion

The paper has some topics that may lead to hot discussions at the next developer or DBA regulars’ table. And sometimes controversial discussions will strengthen your current opinion. After reading the paper I still feel comfortable about my point of view:

Do use foreign keys. Have a good reason for every time you decide against a constraint in the database.