Setting up constraints for your database

You can use physical file constraints to control data integrity in your database. You can add constraints using the Add Physical File Constraint (ADDPFCST) command or System i® Navigator.

To add a physical file constraint, use the ADDPFCST command.

To add a unique constraint, specify a value of *UNQCST on the Type parameter. You must also specify one or more field names for the Key parameter.
To add a primary key constraint, specify a value of *PRIKEY on the Type parameter. The key that you specify on the command becomes the primary access path of the file. If the file does not have a keyed access path that can be shared, the system creates one. You must also specify one or more field names for the Key parameter.
To add a check constraint, specify a value of *CHKCST on the Type parameter. You must also specify a check constraint expression on the CHKCST parameter. The check constraint expression has the same syntax as the expression used for check conditions that are defined by using SQL.

You can also add constraints by using System i Navigator or when you are using the SQL CREATE TABLE and ALTER TABLE statements.

Rules for setting up constraints

Here is a list of rules that apply to all physical file constraints:

The file must be a physical file.
A file can have a maximum of one member, MAXMBR(1).
A constraint can be defined when the file has zero members. A constraint cannot be established, however, until the file has one, and only one, member.
A file can have a maximum of one primary key constraint, but might have many unique constraints.
There is a maximum of 5000 constraint relations per file. This maximum value is the sum of the following constraints:
- The unique constraints
- The primary key constraint
- The check constraints
- The referential constraints, whether they are participating as a parent or a dependent, and whether the constraints are defined or established.
Constraint names must be unique in a library.
Constraints cannot be added to files in the QTEMP library.
Referential constraints must have the parent and dependent file in the same auxiliary storage pool (ASP).