 | |||
|
|
||
| |||
| |||
| |
|
Home > AS/400 Tips > iSeries programmer tips > The ins and outs of using constraints |
| |
|
|
|
| iSeries 400 Tips: |
|
|
|
|
|
|
|
|
|||||
|
ISERIES PROGRAMMER TIPS The ins and outs of using constraintsPaul Tuohy 12.15.2004 Rating: -4.53- (out of 5)
|
[IMAGE] | |||||
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.
[TABLE]Figure 1: Constraint tabs in the properties of a table (physical file)<
Digg This!
 StumbleUpon
 Del.icio.us
|
 |
 |
| |
RELATED GLOSSARY TERMS
|
||||
|
|
| |
RELATED RESOURCES
|
/p>
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.
[TABLE]Figure 2: Defining a Primary Key constraint
The same constraint could be defined on green screen using the following command:
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.
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.
[TABLE]Figure 3: Defining a Referential constraint
The same constraint could be defined on green screen using the following command:
The possible delete rules are as follows:
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).
[TABLE]Figure 4: Defining a Check constraint
The same constraint could be defined on green screen using the following command:
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.
[TABLE]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:
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?"
|
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. |
|
|||
