Systems Engineering and RDBMS

Back to the Basics: Foreign Keys

Posted by decipherinfosys on June 9, 2007

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.

CREATE TABLE TEST_PARENT
(
CASE_NBR VARCHAR(10) NOT NULL,
CASE_SEQ_NBR NUMBER(5) NOT NULL,
ITEM_ID VARCHAR(10),
QTY NUMBER(5,2) DEFAULT 0 NOT NULL,
CONSTRAINT PK_TEST_PARENT PRIMARY KEY(CASE_NBR,CASE_SEQ_NBR)
)
/

CREATE TABLE TEST_CHILD
(
SRL_NBR VARCHAR(15) NOT NULL,
CASE_NBR VARCHAR(10),
CASE_SEQ_NBR NUMBER(5),
SRL_QTY NUMBER(5,2) DEFAULT 0 NOT NULL,
CONSTRAINT PK_TEST_CHILD PRIMARY KEY(SRL_NBR)
)
/

ALTER TABLE TEST_CHILD ADD CONSTRAINT FK_TEST_CHILD_TO_TEST_PARENT
FOREIGN KEY(CASE_NBR,CASE_SEQ_NBR) REFERENCES
TEST_PARENT(CASE_NBR,CASE_SEQ_NBR)
/

CREATE INDEX FK_TEST_CHILD_TO_TEST_PARENT ON TEST_CHILD
(CASE_NBR,CASE_SEQ_NBR)
/

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:

https://decipherinfosys.wordpress.com/2007/05/23/un-indexed-foreign-keys/.

Let us create data in parent table now.

SQL> INSERT INTO TEST_PARENT VALUES(‘CASE001′,1,’ITEM01’,5);

1 row created.

SQL> INSERT INTO TEST_PARENT VALUES(‘CASE002′,1,’ITEM03’,15);

1 row created.

Now we create record into child table. Here we are omitting CASE_SEQ_NBR column purposely.

SQL> INSERT INTO TEST_CHILD(SRL_NBR,CASE_NBR,SRL_QTY) VALUES(‘11111′,’CASE001’,1);

1 row created.

SQL> INSERT INTO TEST_CHILD(SRL_NBR,CASE_NBR,SRL_QTY) VALUES(‘11112′,’CASE007’,1);

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.

SQL> UPDATE TEST_CHILD
2 SET CASE_SEQ_NBR = 3
3 WHERE CASE_NBR = ‘CASE001’
4 /
UPDATE TEST_CHILD
*
ERROR at line 1:
ORA-02291: integrity constraint (DECIPHER.FK_TEST_CHILD_TO_TEST_PARENT) violated – parent key not Found

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.

SQL> UPDATE TEST_CHILD
2 SET CASE_SEQ_NBR = 1
3 WHERE CASE_NBR = ‘CASE001’
4 /

1 row updated.

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.

2 Responses to “Back to the Basics: Foreign Keys”

  1. […] Foreign Keys Identity Inserts […]

  2. […] Back to the Basics: Foreign Keys […]

Sorry, the comment form is closed at this time.

 
%d bloggers like this: