Implementing Referential Constraints in Referential Integrity

by Jim Coker

In my last article, "Referential Integrity: Ensuring Your Database Contains Only Valid Data," I discussed the different concepts involved in Referential Integrity. In that article, I reviewed the two types of constraints available in RI: a "referential" constraint, in which a column in a table is verified to be consistent with a column in another table, and a "check" constraint, in which a column in a table is verified to be consistent with a hard-coded value or the value of another column within that same table.

The purpose of this article is to demonstrate the basic principles involved in implementing RI using OS/400 V5R1. Let's begin with a referential constraint. In this type of constraint, I want the database, not my application, to enforce the relationship between two tables. One perfect application for a referential constraint is in a parent-child or header-detail application. For example, one common application is order entry, in which I have a header table with one row for every customer order taken. Then, in addition, I have a detail table with one row for every line item appearing in the order.

Let's assume these tables are named ORDHDR and ORDDTL. This demonstrates a "one-to-many" relationship. For every one row appearing in ORDHDR, there is one or more rows in ORDDTL. The primary key for ORDHDR is something that uniquely identifies each order. In my example, I will assume that to be an order number, stored in a column named ORDNBR.

The primary key for ORDDTL is something that uniquely identifies each line item within a given order. In my example, I will assume that to be an order number, stored in a column named ORDNBR, along with a line number, stored in a column named LINNBR.

The database design requires the one-to-many relationship to be valid for all orders. That is, there must never be a case in which I have a row in ORDHDR whose ORDNBR value does not appear in at least one row in ORDDTL. In addition, there must also never be a case in which I have a row in ORDDTL whose ORDNBR value does not appear in exactly one record in ORDHDR.

I can control the restriction of no more than one instance of any given order number appearing in ORDHDR by specifying a unique primary key for that table. If an application attempts to add a row to ORDHDR with the same ORDNBR value as any other existing ORDHDR row, the database will return a "duplicate key" violation and prevent the insert from occurring.

But how do I ensure the relationship between ORDHDR and ORDDTL? The answer is found with RI. I can add a physical file constraint that will guard against any anomalies creeping into my data, in much the same way that the unique, primary key kept duplicate keys from appearing.

Creating the Example Tables

To demonstrate, let's run some sample commands using Interactive SQL/400. (Note that you can use any SQL interface to accomplish these steps.) First, create a collection in which to set up our examples. We will call ours "DB2RI" in this example:

CREATE COLLECTION DB2RI WITH DATA DICTIONARY

For those of you who are not familiar with the concept of a COLLECTION in SQL, this will result in an OS/400 library being created on the system. The DATA DICTIONARY simply sets up some system tables to keep track of tables and columns in the library, along with some journals that are used in SQL processing. You could use the CREATE DATABASE statement to accomplish the same thing.

Next, create the two sample tables, ORDHDR and ORDDTL. I have kept the columns of these tables extremely minimal in order to demonstrate the functionality of RI. Each table is created with a primary key, as described earlier:

CREATE TABLE DB2RI/ORDHDR (ORDNBR INT NOT
NULL WITH DEFAULT, CUSNBR INT NOT NULL WITH
DEFAULT, ORDDAT DATE NOT NULL WITH DEFAULT,
PRIMARY KEY (ORDNBR)) 

CREATE TABLE DB2RI/ORDDTL
(ORDNBR INT NOT NULL WITH DEFAULT, LINNBR
SMALLINT NOT NULL WITH DEFAULT, ITMNBR CHAR (10)
NOT NULL WITH DEFAULT, ORDQTY SMALLINT NOT NULL
WITH DEFAULT, UNTPRC DEC (7,2) NOT NULL WITH
DEFAULT, PRIMARY KEY (ORDNBR, LINNBR))

Adding the Referential Constraint

You are now ready to add the referential constraint to the tables. In our case, the referential constraint is going to be added to the ORDDTL table, since it is the one that has the foreign key requirement. For a discussion of foreign keys, refer to the previous article. We will first add the constraint using a SQL statement, ALTER TABLE.

ALTER TABLE DB2RI/ORDDTL ADD FOREIGN KEY (ORDNBR) REFERENCES 
DB2RI/ORDHDR (ORDNBR) ON DELETE NO ACTION ON UPDATE NO ACTION

You also should be aware that you could add a constraint through a regular CL command. To add the same constraint in this manner, enter the following command:

ADDPFCST FILE(DB2RI/ORDDTL) TYPE(*REFCST) 
KEY(ORDNBR) PRNFILE(DB2RI/ORDHDR)

Furthermore, you could also use Operations Navigator to add a constraint. It really makes no difference how the constraint is added. The main point is that, regardless of the method used, once added the database will enforce the constraint 100 percent of time for 100 percent of the applications using the tables.

Adding Data with the Referential Constraint

At this point, we can add rows of sample data to the two tables. Again, this example is using Interactive SQL/400 as the interface. However, RI is in effect at the database level and not the application level. Therefore, it is not important what interface is used to manipulate data in the tables. The same principles would apply if we were using a high-level language, like RPG.

INSERT INTO DB2RI/ORDDTL VALUES(123, 1, 'ABCD', 5, 10.00)

We immediately receive a diagnostic error, SQL0530, indicating:

Operation not allowed by referential constraint 
QSYS_ORDNBR_00001 in DB2RI.

We received this error because we tried to insert a row into ORDDTL with an ORDNBR value of 123. However, there was no associated row in ORDHDR with an ORDNBR value of 123. If we had been allowed to insert the row, we would have violated our parent-child relationship.

On the other hand, if we add a row to ORDHDR first, we will see that both the ORDHDR row and the ORDDTL row may be inserted because our parent-child relationship is intact.

INSERT INTO DB2RI/ORDHDR VALUES(123, 999, '2002-12-31')
INSERT INTO DB2RI/ORDDTL VALUES(123, 1, 'ABCD', 5, 10.00)

Defining ON DELETE and ON UPDATE

You might have noticed that we included a clause ON DELETE NO ACTION ON UPDATE NO ACTION as part of the ALTER TABLE statement. This is a very important clause, and the developer must understand what the different options will do when defining the RI constraint. By specifying NO ACTION for the ON DELETE option, we are instructing DB2 UDB to simply disallow the DELETE statement that will result in violating the referential constraint. Likewise, specifying NO ACTION for the ON UPDATE option will also simply not allow the update. In both cases, a diagnostic message will be returned to the application and the row operation will fail.

Another option for ON DELETE and ON UPDATE is the RESTRICTED option. In this scenario, DB2 UDB will disallow the row operation immediately without further consideration. You might wonder what the difference is between the NO ACTION and RESTRICTED options. The answer is that the NO ACTION option will wait until all triggers defined for the table have been executed.

As an example, let's take a situation in which we have the earlier example rows in our database. That is, we have an ORDHDR row with a key value of 123. We also have an ORDDTL row with a key value of 123. Then, let's assume that the parent table, ORDHDR, has a trigger program DELDTL defined with TRGTIME(*BEFORE) and TRGEVENT(*DELETE). This means that the program will be called before a row is deleted from ORDHDR. Furthermore, let's assume that DLTDTL will take an order number and delete all ORDDTL rows in which that order number appears. If this were the case, deleting a row from ORDHDR would cause the trigger program to delete all dependent rows in ORDDTL.

Given that scenario, we can show the difference between NO ACTION and RESTRICTED. If you choose NO ACTION, RI will allow the trigger program to be executed before inspecting the RI condition. In that case, it would be okay to delete a row from ORDHDR because the trigger program would remove the related rows in ORDDTL before RI inspected the results. The delete operation would be allowed. On the other hand, if you specified RESTRICTED, in which case trigger programs are not allowed to execute before checking RI conditions, the ORDDTL rows would not be deleted and RI would prevent the ORDHDR row from being deleted.

The other options for ON DELETE include CASCADE, SET NULL, and SET DEFAULT. If you choose CASCADE, DB2 UDB will automatically delete all dependent records if you delete the parent. SET NULL and SET DEFAULT are very similar and may be defined only when the foreign key allows null or default values. In this case, deleting the parent row will result in all associated dependent rows having their foreign key fields set to NULL or the defined default value, respectively.

The ON UPDATE clause will only permit you to specify NO ACTION or RESTRICTED.

Dropping a Constraint

If you decide that you no longer want a constraint, you may remove it by issuing another ALTER TABLE statement. The following example will remove the constraint that we created above:

ALTER TABLE DB2RI/ORDDTL DROP FOREIGN KEY QSYS_ORDNBR_00001 CASCADE

When dropping a constraint, the CASCADE option instructs DB2 to also drop any constraints that are dependent upon the constraint specified in this ALTER TABLE statement.

Now You've Got Integrity

In this article, I have touched upon the Referential Constraint, in which I enforced the relationship between two tables. I described a typical one-to-many relationship and created some example tables in order to demonstrate. A referential constraint was created using both SQL and a CL command. When I attempted to violate the constraint by adding an orphan record, I was prevented from doing so by DB2. Finally, I showed you how remove a constraint.

Now that you have looked at the concepts of Referential Integrity and stepped through an example of a referential constraint, you are halfway through the journey. In my next article, I will continue with an example of the other half of RI: the Check constraint. After a discussion of check constraints, along with some rules and "gotchas," you will be ready to jump into RI when designing your applications!

Jim Coker is the IT Director for Behr Process Corporation in Santa Ana, California. He has worked on the AS/400 and iSeries since 1989. He can be reached at jcoker@behr.com.

Sponsored By TEAMSTUDIO

FREE Web-to-Host Tool Evaluation! Take Teamstudio Screensurfer for a FREE test drive! Discover firsthand how this host integration tool enables Web developers to quickly and easily integrate host systems with robust Web applications. Screensurfer is easy to install and activate, and works with all popular application servers. To sign up for your free test drive, click here: www.teamstudio.com/screensurferms