Ins and Outs of Constrains:

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.
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.
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.)
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.
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.
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')
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.