Referential integrity is a major enhancement to DB2/400 announced in V3R1. It allows you to define the relationship between two files-a parent file and a dependent file. For example, the business rule that requires a valid customer number in every order header record can be enforced using referential integrity.

A referential constraint defines the relationship between a parent file and a dependent file. The constraint specifies a rule for insert (add) transactions, a rule for update (change) transactions, and a rule for delete transactions. (For more background information on referential integrity and how referential constraints are defined in OS/400, see "DB2/400 Referential Integrity," MC, September 1995 and the references listed at the end of this article.)

Defining referential constraints is only the first step in the design process; the next question is what to do when a transaction does not conform with the referential constraint. The solution is entirely application dependent. For example, you could issue an error message to the user, you could attempt to correct the problem and reapply the transaction, or you could create a special audit report for transactions that fail. No matter what you choose to do with the error, the first step is to trap it and avoid a system error message.

Once a referential constraint is established with the Add Physical File Constraint (ADDPFCST) command, it is strictly enforced by OS/400. If any application attempts to insert, update, or delete a record that does not conform to the rules of the referential constraint, the transaction will not be completed. This condition is a referential constraint violation.

The mechanisms available to trap referential constraint violations are similar to those used to trap other database errors, such as duplicate keys. If the referential constraint violation is not trapped, a system error message is issued. I'll illustrate referential integrity error trapping with a simple Integrated Language Environment (ILE) RPG program and discuss the support that is available in other development environments.

Trapping referential integrity constraint violations in a well-designed application is usually redundant. Just as you check for duplicate keys before attempting to add a record to a uniquely keyed file, your application can check the business rule enforced by the referential constraint before you actually attempt to update the file. Using the order header relationship with the customer master file as an example, the order entry program chains to the customer master file to determine whether or not the user has entered a valid customer number. If the customer number is invalid, an error message is displayed and the user has an opportunity to correct it. In this case, the program never attempts to create an invalid record and the constraint is never violated.

So why bother to trap referential constraint violations? It's a fail-safe mechanism. Referential integrity will be enforced regardless of any application error. It seems important to at least notify the user that the transaction did not complete. This fail-safe code is similar to including a "Customer not found" message in an open order report. In theory, every order should have a valid customer number. In practice, programmers try to provide accurate information even if the data contains errors. If you issue an error message when a referential constraint is violated, you can avoid confusing the user.

In addition to providing a final check on insert and update transactions, referential integrity error trapping may be the best way to handle the complexity of referential constraint rules for delete transactions. Delete transactions are always enforced from the perspective of the parent file. 1 briefly summarizes the valid rules for a delete constraint.

In addition to providing a final check on insert and update transactions, referential integrity error trapping may be the best way to handle the complexity of referential constraint rules for delete transactions. Delete transactions are always enforced from the perspective of the parent file. Figure 1 briefly summarizes the valid rules for a delete constraint.

To illustrate how referential integrity can simplify your code for delete transactions, suppose that you've specified a delete rule of No Action for the order header file relationship with the customer master file. If you attempt to delete a customer master record for a customer who has outstanding orders, the transaction fails. By trapping the referential constraint violation in the customer maintenance program, you can issue an error message without the overhead of defining the order header file and attempting to read records for the customer. If you use a delete rule of Cascade, you can issue a message if the transaction completes successfully. For example, "All outstanding orders for this customer have been deleted."

No matter what design decisions bring you to the point of trapping referential constraint violations, the process itself is very simple. In RPG, you can trap the file information data structure (INFDS) status code. I'm assuming that you are familiar with the error processing provided by the INFDS. (For more information, see the ILE RPG manuals). Other languages provide similar generic error processing.

To illustrate referential integrity error trapping, I've written an ILE RPG program that attempts to update a record in the order header file. I've defined a referential constraint that requires a valid customer number in the order header file. The program uses a display file that makes it easy to see what happens when a constraint is violated. 2 contains the RPG program, which has been stripped down to the basics for illustration purposes. For example, it doesn't even issue an error message if an invalid order number is keyed.

To illustrate referential integrity error trapping, I've written an ILE RPG program that attempts to update a record in the order header file. I've defined a referential constraint that requires a valid customer number in the order header file. The program uses a display file that makes it easy to see what happens when a constraint is violated. Figure 2 contains the RPG program, which has been stripped down to the basics for illustration purposes. For example, it doesn't even issue an error message if an invalid order number is keyed.

The important features of this program are the INFDS assigned to the ORDHDR file and the processing for referential integrity errors in the error handling subroutine. The error indicator on the UPDATE op code traps any type of system error on this operation, but the program ignores all errors except referential constraint violations. If a referential constraint is violated, the INFDS status code (assigned to field STATUS) will contain either 01222 or 01022. If either of these codes is returned, the program displays an error message.

In a real application, once you trap the status code, you must decide what to do with the information. For example, you can call the customer master maintenance program, allow the user to create a new customer, and rerun the update transaction. Alternatively, you can display an error message. You could even ignore the referential constraint violation. By trapping it in the RPG program, you avoid a hard halt issued by OS/400, and the user will not know that the transaction failed unless your program provides that information. I do not recommend ignoring the error. At a minimum, you should notify the user that the transaction failed.

I chose ILE RPG for the sample program because it has the ability to trap specific referential constraint violation status codes. Original Program Model (OPM) RPG will return a generic file error status code (01299) instead. This creates a problem since many other conditions could potentially cause this file error. Since referential integrity, like ILE RPG, is delivered beginning with V3R1, I recommend that you convert programs that need to handle referential constraint violations to ILE RPG.

The same rule applies to COBOL. ILE COBOL has the ability to isolate referential integrity constraints with a file status of 9R; OPM COBOL returns file status 90.

SQL provides a series of error codes for referential constraint violations. Based on a simple test, these codes will be returned to an environment that uses SQL to access DB2/400. In the test, a client/server application attempted to write a record that violated a referential constraint. SQL code 530 was returned to the application. As with my RPG example, it remains the application developer's responsibility to trap the error and return information to the end user.

Doctors attempt to prevent disease before it happens-with education, sanitation, and vaccinations. When disease does break out, they apply more drastic measures-antibiotics and even surgery. In designing applications, similar goals apply. Referential integrity is not a panacea for a poorly designed database or sloppy programming. Consider it a safety net-if an application fails to trap a referential constraint violation, the database is still protected.

The simple error trapping illustrated in this article can help you protect users from system error messages. You may even find that there are cases when you want to use a referential constraint to help validate data. By choosing a development environment that returns specific errors when a constraint is violated and gracefully trapping those errors, you can expand your design options to take advantage of referential integrity.

Sharon Hoffman is the editor of Midrange Computing. She can be reached by E-mail at This email address is being protected from spambots. You need JavaScript enabled to view it..

REFERENCES

DB2/400 Advanced Database Functions Redbook (GG24-4249, CD-ROM GG244249).

ILE RPG/400 Programmer's Guide (SC09-1525, CD-ROM QBKAQD00).

ILE RPG/400 Reference (SC09-1526, CD-ROM QBKAQE00).

OS/400 DB2/400 Database Programming V3R1 (SC41-3701, CD-ROM QBKAUC00).

Referential Integrity Error Trapping

Figure 1: Delete Constraint Rules

Rule Description

No Action Prevents deletion of the parent file record if there are any records in the dependent file that reference that record. Constraint violations are checked after other database events.

Restrict Same as No Action except for the timing of the check for dependencies. Constraint violations are checked before other database events.

Cascade Deletes all the records in the dependent file that are based on the parent file record being deleted.

Set Default Sets the value of the foreign key (the field in the dependent file that references the parent file) to its default. A parent file record with the default key must exist.

Set Null Sets the value of the foreign key fields that allow nulls to nulls. The foreign key must contain at least one null-capable field.

Referential Integrity Error Trapping

Figure 2: ILE RPG Error Trapping Example

 *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7 * * Illustration of referential integrity error handling * FORDHDR UF E K DISK INFDS(INFDS) FDB001DF CF E WORKSTN * Define the INFDS to capture status information DINFDS DS DSTATUS *STATUS C DOW *IN03 <> *ON * Interactive example to show errors on screen C EXFMT RITEST C EVAL *IN98=*OFF C MOVE *BLANK ERRMSG C OHNUM CHAIN ORDHDR 99 C *IN99 IFNE *ON C EVAL OHCUST=NWCUST * If any error on update 98 will be set on C UPDATE OHHDR 98 C ENDIF * Call the ERROR subroutine explicitly C *IN98 IFEQ *ON C EXSR ERROR C ENDIF C ENDDO C MOVE *ON *INLR * Error processing C ERROR BEGSR * Ignore all errors except RI errors C STATUS IFEQ 01222 C EVAL ERRMSG='RI error 01222' C ELSE C STATUS IFEQ 01022 C EVAL ERRMSG='RI error 01022' C ENDIF C ENDIF C ENDSR *. 1 ...+... 2 ...+... 3 ...+... 4 ...+... 5 ...+... 6 ...+... 7