Foreign Key Constraints: Friend or Frenemy?

In the early 1970's, Edgar F. Codd, a pioneer of the relational model for databases, gave us the rules of normalization to help us design and maintain relational databases. These rules have been at the core of relational database design for over 40 years now. Unfortunately, these great ideas are still often poorly implemented, either through the lack of understanding of Codd's rules, organic growth, or too little time to implement a design correctly.

Related: Tips for Using Foreign Keys

It's kind of like the way we drive. We know what the speed limit is in our neighborhood, and we know that we are required to wear a seatbelt while driving, but we are just going down to the corner to pick up some ingredients for dinner that we forgot to purchase at the store earlier. Pretty soon, it becomes a habit of not wearing a seat belt and speeding through the neighborhood. At least it's our habit until we're pulled over for speeding and are also cited for the lack of a seatbelt. Oops. (No, this did not happen to me.)

Living up to "the letter of the law" for Codd's rules, but not following through to make sure they are adhered to, is the same thing as continuing to break driving laws. Not following the rules becomes a bad habit that you have to pay for later. Thank goodness we have SQL Server Integrations Services (SSIS) to clean up data messes. However, it would be nice if we could simply use SSIS to combine and aggregate data and not for cleaning up messes that should never have been in our database in the first place causing inaccurate metrics and implementation headaches as a database matures. Let's take a look at a few common bad habits that need to be broken in order to provide better, consistent data.

Foreign Key Constraints Are Important Too

Codd's first rule states that the data in the database should not be repeated. Data should be uniquely represented. Once we apply this rule to our tables, we end up with the data broken-up across several tables. An example of this would be a CustomerOrder table that contains Orders with a foreign key to an OrderState table that tells us what State our Customer Order is currently at, and a SalesAssociate table that tells us which Sales Associate created the Order for our Customer, as shown in Figure 1.

Figure 1: Foreign Keys are present, but not foreign key constraints

If one more step is taken, adding of foreign key constraints, then many headaches can be prevented in the future as show in Figure 2. The beauty of foreign keys is two-fold. First, and foremost, adding constraints guarantee that the data in the foreign keys are valid. Second, adding constraints doesn't allow the data from the primary table that is being used in a foreign key, to be deleted.

Figure 2: Foreign key constraints have been added to the tables

Let's take a look at some of the silent killers that I have seen when foreign key constraints are not added to the database model. I'll continue to use the tables I mentioned above in my discussion.

There Are Dust Bunnies in the Tables

In the following scenario, all three tables exist, but bad data is showing up in the CustomerOrder table. Here are some of the symptoms that are appearing.

After comparing the data in the OrderStateID and SalesAssociateID fields to the tables that the values should be coming from, OrderState and SalesAssociate respectively, it is discovered that the tables are out-of-date. Nobody has been maintaining the tables, and thus, there are dust bunnies hiding out in there (and they have big sharp fangs).

Foreign key constraints would have prevented these problems. Here's how.

The Case of the Missing Order States

I've seen this problem too many times to count. I inherit a database. There are foreign keys in a table, but the table they should map to is nowhere to be seen. Since there are no foreign key constraints, I can't "follow the lines" to find the table. I have to go through all the tables to verify that the data isn't in a poorly named table or in a named value pair table.

This leads me to a trip to the application developers in search of the Order State values. Here are some of the responses I get.

There are couple of different things you need to do. First, convince the application team to change the code to get the Order State values from the database if possible. Here are some reasons you can use to convince them.

After you convince the application team to make the changes described above, the second thing you need to do is confirm that all the values they give you are being used. The best scenario will be that the data matches on both sides. The worst scenario is that the database has more values than the application has. This will take research to determine if the unaccounted values are bad data, discontinued data, or simply forgotten data.

Once all the data is accounted for, then you can create a table with the values and create a foreign key constraint to the new table.

All of these lurking silent killers can be prevented if the initial implementation had used foreign key constraints to the foreign keys themselves.

Related: Should I Index My Foreign Key Columns?

******************************************************************************

Mickey Stuewe is a database architect. Her focus is on database modeling, T-SQL optimization, requirements gathering, and report design using the SQL Server stack. Stuewe blogs regularly at mickeystuewe.com and co-authored the book, SQL Server 2012 Reporting Services Blueprints. Follow Stuewe on Twitter @SQLMickey.

******************************************************************************