Back to the Basics: Foreign Keys

A couple of days ago, we had posted about the “IDENTITY_INSERT” option in SQL Server in our “Back to the Basics” series. In this post, we will look at one of the commonly overlooked facts about composite columns that make up a foreign key. As you know, a foreign key constraint is essentially a column or a group of columns in the child table that are used to establish a link/reference to the parent table. This constraint can be defined on either the primary key column(s) of the parent table or any other alternate/candidate keys of that table i.e. any other not null column or set of columns that make a record unique in that table. It can also reference the column(s) in the same table in case it is a recursive relationship i.e. a self referencing relationship. The column(s) in the child table on which the foreign key is defined can be nullable.

One thing that folks typically miss though is that if the foreign key is a composite key i.e. created on more than one column, and if any of those columns is a nullable column, then verification of all the values that make up the foreign key constraint is omitted at the time of the insert or the update if a NULL value is passed in for that column. This is because the FK constraint is on the combination and having null values precludes the verification test. This can lead to bad data issues and data corruption issues and should be checked. First, let’s pick up an example and see the issue and then we will go over what can be done to prevent such issues from occurring.

We will create two tables and will use Oracle as an example – same is true for SQL Server and DB2 LUW as well.

Here we have also created index on the foreign key columns. It is very vital that foreign keys are always indexed. To identify un-indexed foreign keys and what happens, if they are not indexed, please look at our previous blog post:

Here, even though we have not populated CASE_SEQ_NBR column, record went through fine without any error. Partial foreign key values were not validated. Likewise, for the CASE007 value which does not even exist in the parent table. Now, let us try to update CASE_SEQ_NBR value with some value which does not exist in the parent table.

Since both columns have values now, it tried to validate the foreign key and threw the error. If we update it with the correct value which exist in the parent table, validation and hence update will be successful.

In short, if there is a partial update to foreign key columns, it won’t be validated and won’t give any error. This is not very common scenario but we have to be little more careful in such cases since we have seen this to be an issue at some of our client sites.

In order to prevent such issues, one can either declare the child table columns that make up the FK constraint as NOT NULL or if that is not feasible from a business perspective, one can chose to create a check constraint on the child table such that when a value is provided for one of those columns and the value is null for the other column, that DML statement should error out. The check constraint would make perfect sense since the combination of columns represents a tuple (row) in the parent table and having partial values won’t make sense from a design or business flow perspective. Another way to avoid this would be that when the application tries to create the record, it can check for the existence of data for both the columns – if one has a value and the other does not, that should be an error condition.

Share this:

Like this:

LikeLoading...

Related

This entry was posted on June 9, 2007 at 12:07 pm and is filed under DB2 LUW, Oracle, SQL Server.
You can follow any responses to this entry through the RSS 2.0 feed.
Responses are currently closed, but you can trackback from your own site.