CL Command Reference - ADDPFCST
CL Command List > ADDPFCST Reference
Description:
The Add Physical File Constraint (ADDPFCST) command can be used to add constraint relationships to a specified physical file. The four types of constraint relationships that you can add are referential constraints, unique constraints, primary key constraints and check constraints. All constraints are defined at the file level.
You can use constraint relationships to define dependencies between files. The relationships that you define are enforced by the system when changes occur to information in the files. When you define constraint relationships you control the referential integrity of the data being processed.
To define or establish a referential constraint, the parent file and the dependent file must exist. However, if the parent or dependent file has no members, the constraint only is defined (not established).
When a referential constraint is established, either an access path is created or an existing access path with matching attributes is shared. A maximum of 300 constraint relationships can be established for a file. However, only one primary key constraint can be established for a file.
You can remove a constraint by using the Remove Physical File Constraint (RMVPFCST) command. You can view all constraints for a dependent file by using the Display File Description (DSPFD) command.
Restrictions:
. You cannot add constraint relationships to system files or to program described files.
. You cannot add a constraint relationship to a file that your user job has open.
. Referential constraints cannot span auxiliary storage pools (ASPs).
. Constraints cannot be added to a file in the temporary library QTEMP.
. If a check or referential constraint is added with this command and the established referential or check constraint has records that are in check pending, the constraint is automatically changed to the disabled state.
. This command is conditionally threadsafe. In multithreaded jobs, this command is not threadsafe for distributed files and fails for distributed files that use relational databases of type *SNA.
Examples:
Example 1: Adding a Unique Constraint
ADDPFCST FILE(MYLIB/LOCATIONS) TYPE(*UNQCST) KEY(REGION)
CST(Personnel_by_REGION)
This command adds a unique constraint to the LOCATIONS file in the MYLIB library. The field that defines the access path is REGION. The name of the access path is Personnel_by_REGION.
Example 2: Adding a Referential Constraint
ADDPFCST FILE(ADMN/PERSONNEL) TYPE(*REFCST) KEY(REGION)
CST(1994Hires) PRNFILE(MYLIB/LOCATIONS)
PRNKEY(REGION) DLTRULE(*CASCADE)
UPDRULE(*RESTRICT)
This command adds a referential constraint to the PERSONNEL file in the ADMN library. The field that defines the access path is REGION, which is also the key for the parent file LOCATIONS in the MYLIB library. The name of the access path is 1994Hires. According to the delete rule of cascade, if a record in the LOCATIONS file is subsequently deleted, and that record matches a record in the PERSONNEL file, the record also will be deleted from the PERSONNEL file. According to the update rule of restrict, subsequent changes to the LOCATION file records defined in the constraint are restricted at the beginning of the update request.
Example 3: Adding a Check Constraint
ADDPFCST FILE(PERSONNEL/SALARY) TYPE(*CHKCST)
CST(Upper_Salary_Limit) CHKCST(¡¯EMPSAL <= 100000¡¯)
This command adds a check constraint to the SALARY file in the PERSONNEL library. The check constraint will ensure an employee¡¯s salary may be a maximum of 100,000.