Get started Bring yourself up to speed with our introductory content.

Check constraint allows database admins to sleep a bit better at night

You can easily prevent errant programs from writing invalid data to a field with a simple constraint.


Ron Turull

One of the most important database features for database administrators is the check constraint. It allows you to easily define the valid values for a field at the physical file level. No longer do you have to fiddle with triggers and trigger programs, or application code. You can now easily prevent errant programs from writing invalid data to a field with a simple constraint.

Good idea, bad implementation
Years ago and soon after I started working with AS/400s, I was looking for a way to protect certain fields in a physical file from being assigned invalid values. Lo and behold, I found salvation in the DDS Reference manual -- or so I thought.

The first line of the description of the Values keyword reads, "At the field level, this keyword specifies validity checking for the field you are defining . . ." Then came one of those moments when you think you have just found a solution only to have your hopes dashed away -- I read the second line: ". . . when it is referred to later during display file creation."

More Information

Well, that was about as useless as IBM's Information Center! It was one of those good ideas that got poorly implemented. The Values keyword does nothing to protect the field at the physical file level. In fact, it doesn't alter the physical file one iota. It comes into play only if the field is referenced in a display file (i.e., you reference the field in the DDS for a display file by specifying an R in position 29 and the REF or REFFLD keyword). The values you specified on the Values keyword then become the only valid values the user can enter for the field in the display file -- not the physical file. OK, so it's a bit more useful than the Information Center.

IBM answers the call
The check constraint is the solution. This type of physical file constraint allows you to put restrictions on which values are valid for a specific field. For example, you could restrict an inventory type field to allow only 'P' and 'N' (perishable and non-perishable). You can also restrict values to within a given range. You can even restrict the value of a field based on the value of some other field in the same record.

You'll need SQL knowledge
The constraint specification itself is entered as an SQL check-condition. That is, to set the restrictions for the field, you have to use SQL syntax. So, you must have some knowledge -- albeit only rudimentary -- of SQL.

If you know some basic SQL, you can think of an SQL check-condition as a simple where clause (e.g., it cannot have complicated elements such as subqueries). If you don't know SQL, consider it a fine time to get on board. SQL is the defacto standard in the entire industry. Not knowing SQL in this day and age is akin to a police officer not knowing how to handle a gun. Besides, for check constraints, we are talking only basic knowledge that you can get in probably an hour or less from any SQL book sold by the dozens in major bookstores. No bookstore nearby? Check out the bookstores on the Web.

So you can see just how easy it is, here are a couple of examples of SQL check-conditions:

* INVTYP = 'P' or INVTYP = 'N'

* INVTYP in ('P', 'N')



The first two examples are equivalent, that is, field INVTYP can be assigned only values 'P' or 'N'. The third example shows you that you can restrict multiple fields with a single constraint. The last example shows how you can restrict the value of a field based on the value of another field (total sales must be less than or equal to the credit limit). Restricting the value of a field based on the value of another field is allowed only when the fields are in the same file; you have to use a trigger if the fields are in different files.

How to add a check constraint to a file
A check constraint is added like any constraint, by using the ADDPFCST (Add Physical File Constraint) command. Here are some examples:



Alternative: Referential constraints
Referential constraints can be an alternative in certain circumstances. For example, to make sure the INVTYP field is only assigned 'P' or 'N', you could set up a INVTYPES file with the DDS source shown below:

                R INVTYPESR

                  TYPE           1
                  TYPDESC       20

                K TYPE

Add two records to it, one with the TYPE field set to 'P' (perishable) and the other with the TYPE field set to 'N' (non-perishable). Then, with the following command, establish a referential constraint to ensure that the INVTYP field in the INVMAST file can be set only to 'P' or 'N':


The one advantage to this alternative is you can easily add (or delete) valid values by simply updating the parent file (the INVTYPES file in the above example).

Alternative: Triggers
If check constraints and referential constraints won't work for you, remember you can always write a trigger program. Because you write the trigger program, it can do whatever you want and, therefore, it can do whatever referential and check constraints can't. For example, when the valid value(s) for a field is based on the value of another field, but the other field is in a different file, a simple trigger will do the job.

About the author: Ron Turull is editor of Inside Version 5. He has more than 20 years' experience programming for and managing AS/400-iSeries systems.


Dig Deeper on iSeries SQL commands and statements

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.