Home > AS/400 Tips > iSeries administrator tips > Check constraint allows database admins to sleep a bit better at night

	iSeries 400 Tips:	EMAIL THIS

	TIPS & NEWSLETTERS TOPICS

	ISERIES ADMINISTRATOR TIPS Check constraint allows database admins to sleep a bit better at night Ron Turull 12.22.2004 Rating: -4.43- (out of 5) Digg This!     StumbleUpon     Del.icio.us    [TABLE] 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." [TABLE]			[IMAGE]

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 s

Digg This!     StumbleUpon     Del.icio.us

RELATED CONTENT iSeries administrator tips Translating Linux for IBM i admins: Using GUI to make it easy Translating Linux for IBM i admins: Working with jobs and networking OpenOffice: What to know before making the transition from Microsoft Office OpenOffice: An enterprise open source solution Database performance comparisons on IBM i Translating Linux for IBM i admins: User profile commands Modern System i reports using Client Access Tips for installing Lotus Domino server on a System i partition The iSeries Blog has a new home on IT Knowledge Exchange Virtualization for IBM i: Backups iSeries SQL commands and statements When is the YES option for 'reuse deleted files' function the best choice? Convert a numeric physical file to a character in SQL without leading zeros Inserting data from a CTE into a file in SQL Implement variables in SQL when creating an alias Control and fix date format problems on AS/400 SQL Disable SQL query optimizer tool that removes trailing blanks Database drivers on the i: MySQL vs. IBM Toolbox How to: Output SQL script to a text file from an AS/400 IBM DB2 Connect allows SQL data sharing Size limit of DB2 table in V6R1 on IBM i iSeries SQL commands and statements Research Performance Extend storage capacity on an IBM i without negatively effecting system performance Database drivers on the i: MySQL vs. IBM Toolbox Performance tuning for IBM i: The basics and beyond IBM releases new Power products for the midrange Top System i admin tips for 2006 Catholic Charities keeps track of homeless with iSeries i5 error messages: What you need to know IBM races for clock speed System shutting down after cleanup What is the relationship between QZDAOSINIT jobs and QDBSRVnn tasks?

RELATED RESOURCES 2020software.com, trial software downloads for accounting software, ERP software, CRM software and business software systems Search Bitpipe.com for the latest white papers and business webcasts Whatis.com, the online computer dictionary

olution. 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:

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:

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.

Rate this Tip To rate tips, you must be a member of Search400.com. Register now to start rating these tips. Log in if you are already a member. Submit a Tip DISCLAIMER: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.