The ins and outs of using constraints

Paul Tuohy take a closer look at constraints, what they are and how you define them.

A couple of months ago I wrote an article about identifying the names of constraint violations in RPG programs

("Name that constraint"). Based on a couple of e-mails I received, I thought it might be worth having a look at constraints, what they are and how you define them.

Constraints are a function of Referential Integrity, where the database manager ensures the logical consistency of data values between files and the validity of data relationships, based on rules set by you. Impressive as that sounds, it is something you are already doing; except that you are doing it in your application programs. For example you cannot delete the customer if there are dependant invoices on the invoice file, and you do not employ people under the age of sixteen. Those constraints are implemented through logic in your RPG or COBOL programs. As your applications expand and data becomes accessible outside of the traditional green screen, it becomes imperative that these rules are consistent across all interfaces. What better way to implement them then through the database manager?

Constraints are defined for physical files or tables. You can define three types of constraint: Key, Referential and Check.

How do you define constraints?

You can define constraints using the Add Physical File Constraint (ADDPFCST) command. You can also use the CHGPFCST, RMVPFCST, WRKPFCST, EDTCPCST and DSPCPCST commands.

You can define them in SQL using the CREATE TABLE or ALTER TABLE commands.

But my favorite means of defining constraints is in iSeries Navigator. Use the Key Constraints, Referential Constraints and Check Constraints tabs from the properties of a table, as shown in Figure 1.

Figure 1: Constraint tabs in the properties of a table (physical file)

Key constraints

Key constraints define unique keys for a table. The end result is an access path, but there is no corresponding logical file. Since DB2 automatically shares access paths, there is no extra overhead if there is already a logical file that defines the access path.

There are two types of Key constraints: unique and primary. A table may have only one primary Key constraint but may have many unique Key constraints.

Figure 2 shows the definition of a Primary Key constraint in iSeries Navigator. The constraint is named CategoryPrimaryKey, and the category code is the key field. (For multiple key fields, simply select them in the required sequence.) Note that constraint names are long names, i.e. they are not object names.

Figure 2: Defining a Primary Key constraint

The same constraint could be defined on green screen using the following command:

ADDPFCST FILE(ALLTHAT1FL/CATEGOR) TYPE(*PRIKEY)             
         KEY(CATCOD) CST(CategoryPrimaryKey)   

Referential constraints

Referential constraints are where you define a constraint between two tables: a parent and a dependant. The parent file must have a primary constraint defined for it.

In this example there is a dependency between the Category file and the Product file. Every product "belongs" to a category. Therefore, you should not be able to delete a category if any products refer to it, and you should not be able to assign a non-existent category to a product. Think how you would manage this in an application -- a logical over the product file that you use to check for existing records in the Category maintenance program and the Product maintenance program checks the Category file to make sure the category code is valid. (But you can bypass all of that with DFU.)

Figure 3 shows the definition of a Referential constraint that will enforce the rule for you. The constraint (CategoryProductRestriction) is defined between the Product file (dependant) and the Category file (parent). The corresponding fields for the constraint are the category codes. The constraint specifies a delete and an update rule of restrict.

Figure 3: Defining a Referential constraint

The same constraint could be defined on green screen using the following command:

ADDPFCST FILE(ALLTHAT1FL/PRODUCT) TYPE(*REFCST)             
         KEY(CATCOD) CST(CategoryProductRestriction)
         PRNFILE(ALLTHAT1FL/CATEGOR) PRNKEY(CATCOD)              
         DLTRULE(*RESTRICT) UPDRULE(*RESTRICT)    

The possible delete rules are as follows:

  • RESTRICT -- Record cannot be deleted if there are dependent records.
  • CASCADE -- It's OK to delete a parent, but all dependent records are deleted as well.
  • SET NULL -- Null-capable fields, in the dependent key, are set to null.
  • SET DEFAULT -- Fields in the dependent key are set to their default values.
  • NO ACTION -- A record cannot be deleted if there are dependent records; however, triggers will be fired before checking Referential constraints.

The possible update rules are RESTRICT and NO ACTION, and the insert rule is implicit. (No records can be added that have no matching parent record.) All rules except RESTRICT require that both the parent and dependant files be journalled to the same journal.

Take a moment to consider a CASCADE delete rule. Your program deletes a record from a parent file, and the database manager deletes 200 corresponding records on the dependent file. You can see why they need to be journalled.

Both the parent and dependent files must be single-member, externally defined files. As with key constraints, a Referential constraint results in the generation of an access path as would be defined by a join logical but without the actual logical file being created.

Check constraints

Check constraints allow you to define validation for columns in a table. The nearest to this in DDS is the COMP, RANGE and VALUES keywords, but they apply only to display files. Check constraints are maintained on the database.

Figure 4 shows the definition of a Check constraint (Right_Price) that ensures that the selling price (SELLPR) is always greater than or equal to the buying price (LNDCST).

Figure 4: Defining a Check constraint

The same constraint could be defined on green screen using the following command:

ADDPFCST FILE(ALLTHAT1FL/PRODUCT) TYPE(*REFCST)             
         KEY(CATCOD) CST(Right_Price)            
         CHKCST('SELLPR >= LNDCST')    

Which constraints?

V5R3 of iSeries Navigator provides a means of listing constraints within a library, but releases prior to V5R3 don't. Use the WRKPFCST command to view the constraints applied within a library, as shown in Figure 5. Note that the constraint name is truncated; pressing F22 will show you the full name, at the expense of some other data on the screen.

The Referential constraint for the product file has a state of established/enabled and has no records with check pending. The State column shows whether a Referential constraint is in a defined (DEF) or an established (EST) state. In a defined state, one of the files does not have a member, i.e. the constraint is defined but cannot be established. An established state exists when both files have members. In either state, the constraint can be enabled or disabled (more on this in a moment).

You also have the option to change or remove constraints or to display records in check pending.

Figure 5: Defining a Check constraint

Check pending means there are records on the file that contravene the Referential constraint; the parent file can be opened only for read and insert operations (no updates or deletions allowed) and the dependent file cannot be opened at all. To fix the data in the case of a check pending status, you will need to temporarily disable the constraint.

At times you might consider it worthwhile to temporarily disable a constraint for performance reasons. For example, suppose a month-end process performs a batch update on a parent file and a dependent file. The month-end process allocates both files, using the Allocate Object (ALCOBJ) command. Based on your confidence that the month-end process will not violate a Referential constraint, you disable the constraint after allocating the files, run the month-end program(s), and re-enable the constraint before de-allocating the files.

Of course, if your confidence about your month-end process was ill-founded and the batch job does create data that violates the constraint, then your files will go into a Check Pending status when you re-enable the constraint and you must correct the data before the files can be used.

Violating constraints

How does a program know it has attempted to violate a constraint? The database manager generates a file error, which you trap in your program. (See "Name that constraint" for details on handling constraint errors in RPG.) Some file errors:

  • ILE RPG -- Use the %Error BIF with an E extender and check for status codes 01222 and 01022
  • ILE COBOL -- File status 9R
  • ILE C -- Mapped to existing error numbers
  • SQL -- SQLCODES 530, 531, 532

In the end . . .

Referential Integrity is a powerful tool for us to use in our applications and provide a means of ensuring data integrity outside of our application. It's well worth your consideration.

---------------------------
ABOUT THE AUTHOR: Paul Tuohy is CEO of ComCon, an iSeries consulting company. He is the author of Re-Engineering RPG Legacy Applications and is one of the quoted industry experts in the IBM Redbook "Who Knew You Could Do That With RPG IV?"


 

This was first published in December 2004

Dig deeper on iSeries CL programming

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchEnterpriseLinux

SearchDataCenter

Close