Are foreign keys really necessary in a database design?

Aug 22 2008

Recently I asked a question in StackOverFlow:

Are foreign keys really necessary in a database design?

As far as I know, foreign keys are used to aid the programmer to manipulate data in the correct way. Suppose a programmer is actually doing this in the right manner already, then do we really need the concept of foreign keys?

Are there any other uses for foreign keys? Am I missing something here?

Many people responded and there were all kinds of opinions. The strange thing I noticed was that many people including Joel Spolsky do not use foreign key constraints in their databases.

You can build a complete application without even using a single foreign key constraint. Foreign keys, as a matter of fact are not essential in your databases, but they help a lot in programming and making robust systems. Hence the use of foreign key integrity constraints is strongly recommended.

These are the obvious advantages of using foreign key constraints:

  1. They help in keeping data integrity by removing the possibility of unwanted data piling up and cluttering the database. Mostly this data will not affect your business logic, but eventually you will have to take up the task of cleaning up the mess manually.
  2. They help in visualizing and preparing database diagrams. Many tools are available which will automate this task.
  3. They may give performance benefits. In systems which auto index fields, foreign key references can give a performance boost.
  4. Foreign keys can also help the programmer write less code using things like ON DELETE CASCADE and other referential actions.

If you know of any other advantages please add them below.

Curtis Poe has written a very good article about this mentioning the fundamental flaw in the thought against foreign keys:

If you have an SQL statement inserting data, you want the receiving table to have the data validation in the form of foreign keys, custom data types, enums, triggers and so on. If you’re writing code for personal use or you control an open-source project, skip this if you want. It’s your code; it’s your choice. However, if you’re writing code for a business, you don’t know who will be maintaining it tomorrow. Maybe they don’t know about your clever data validation routines which allow you to forego foreign keys. Maybe they’re finding your code too slow so they decide to write directly to the database themselves. Whatever the reason, by putting the data validation as close as possible to the data munging, you make it harder to circumvent.

In effect, foreign key constraints are a way of implementing a set of guidelines for proper querying and manipulation of data in a database. You can do away with them, but you are bound to make mistakes eventually without them.

47 responses so far

  • pranav says:

    Very nice article and discussion , thanks for comments from Antelio and Mahesh for exploring more about foreign keys.

  • Tom says:

    If everybody wrote (and maintained) perfect business logic, there would be no need for foreign keys, but that’s not the case. The main reason for a foreign key is to flag a logic error that would result in an integrity fault, before it leads to a larger mess. In short it’s a debugging capability, a very good and useful crutch. Nothing wrong with that.

  • mipo says:

    Thank you so much. that is useful article.

  • well I prefer to use foreign key as you yourself has mentioned several benefit of using it. but since with the growth in internet technology and focus is mainly on nosql side including twitter, facebook, amazon and google. But on small scale using database properly does makes your life easy.

  • DuanyDuck says:

    I realized that using only foreing keys without index could slow down the speed of reading, but abuse of index creation will slow down the modifying / deleting data.
    The benefits of using foreing keys make it worth .

  • Hey there! Would you mind if I share your blog with my facebook group?
    There’s a lot of people that I think would really enjoy your content.

    Please let me know. Many thanks

  • mobile games says:

    Good day! I know this is kinda off topic but I was
    wondering if you knew where I could get a captcha plugin for my comment form?
    I’m using the same blog platform as yours and I’m having problems finding one?
    Thanks a lot!

Leave a Reply