Thursday, May 06, 2010

When you can do without foreign keys

I had a boss once who said, after I'd reported that a database design was done except for the foreign keys, "Don't do that -- just a bunch of overhead, and if you code it right you don't need them."

I didn't say anything, but I remember thinking, "I had a ten year career fixing bad data!  A database that can defend itself against corruption is a wonderful thing!"

Someone else recently said to me that they didn't use to believe in foreign keys for the same reason:  if you write your business layer right, you don't need them to protect your database.  So the following is my summary of when you can get away with not having foreign keys in your database:
  1. If you are the only programmer on the app;
  2. If you are the only programmer who ever has or ever will work on this app;
  3. If no apps not under your control can make any changes to your database;
  4. If no ad-hoc changes or fixes are ever made to the database;
  5. If you and your tester (who may also be you) are blessed with the following conditions:
    1. Lots of experience;
    2. Plenty of time;
    3. Great thoroughness;
    4. A good memory for detail;
    5. Perfect understanding of both the database and app design and of the business problem
... then you can do without foreign keys.

But -- seriously, I'm asking -- why would you want to?

0 Comments:

Post a Comment

<< Home