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:
- 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.
- They help in visualizing and preparing database diagrams. Many tools are available which will automate this task.
- They may give performance benefits. In systems which auto index fields, foreign key references can give a performance boost.
- 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.