ADDPFCST (Add Physical File Constraint)

ADDPFCST Command syntax diagram

 

Purpose

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 or check constraints. All constraints are defined at the file level.

Use referential 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

  1. You cannot add constraint relationships to system files or to program described files.

  2. You cannot add a constraint relationship to a file that your user job has open.

  3. Referential constraints cannot span auxiliary storage pools (ASPs).

  4. Constraints cannot be added to a file in the temporary library QTEMP.

  5. If a referential constraint is added with this command and the established referential constraint has records that are in check pending, the constraint is automatically changed to the disabled state.

  6. In multithreaded jobs, this command is not threadsafe for distributed files and fails for distributed files that use relational databases of type *SNA.

 

Required Parameters

FILE
Specifies the file to which a constraint is being added. The file must be a physical file and it must allow a maximum of one member (MAXMBRS(1)).

If a referential constraint is being added, this parameter specifies the dependent file and the library containing the dependent file. The parent file is specified on the PRNFILE parameter.

The name of the physical file can be qualified by one of the following library values:

*LIBL: All libraries in the job's library list are searched until the first match is found.

*CURLIB: The current library for the job is searched. If no library is specified as the current library for the job, the QGPL library is used.

library-name: Specify the name of the library to be searched.

physical-file: Specify the name of the physical file.

TYPE
Specifies the type of constraint being added to the physical file.

*REFCST: A referential constraint is being added.

 

Notes

  1. Referential constraints cannot span multiple ASPs (auxiliary storage pools).

  2. Referential constraints cannot be added while either the parent or the dependent file is open.

  3. Duplicate and multiple referential constraints can be added between the same dependent and parent files if the constraint name is unique. However, the results may not match your expectations. See the Database Programming topic in the Information Center for advisory information on duplicate or multiple referential constraints.

*UNQCST: A unique constraint is being added.

Note: Duplicate unique constraints are not allowed.

*PRIKEY: A primary key constraint is being added. A primary key constraint is a special case of a unique constraint.

Note: Only one primary key constraint is allowed per physical file.

*CHKCST: A check constraint is being added. A check constraint is a field-level validity check of the data in a record of a physical file.

KEY
Specifies the constraint key, which is the definition of the access path for the type of constraint specified on the TYPE parameter. The constraint key is one or more fields that exist in the file specified on the file parameter. For all constraint types, the fields specified can allow nulls (ALWNULL). If a primary key has at least one null capable field, a check constraint will also be added which will prevent null values from being inserted into the null capable fields. This check constraint cannot be disabled and will be removed when the primary key is removed.
*REFCST
The foreign key of a referential constraint is defined. If a referential constraint is established, a foreign key access path is added to the dependent file.
*UNQCST
The key of a unique constraint is defined. If a unique constraint is established, a unique key access path is added to the physical file.
*PRIKEY
The key of a primary key constraint is defined. If a primary key constraint is established, a primary key access path is added to the physical file.

field-name: Specify the name of the field for the constraint key you are defining. Each field name must exist in the file specified on the FILE parameter. You can specify a maximum of 120 (but no duplicate) field names to define the constraint key, where:

  • The field names are of the object type *NAME and are a maximum length of 10.

  • The fields must be specified in ascending order.

  • The maximum number of bytes in a key is 2000 bytes (see the Database Programming topic in the Information Center for more information on this limitation).

 

Optional Parameters

CST
Specifies the name of the constraint being added.

*GEN: The system generates a constraint name.

constraint-name: Specify the name of the constraint. The constraint name must be unique to the library of the physical file specified on the FILE parameter. You can specify a maximum of 128 characters without delimiters, or 256 characters with quotation mark (") delimiters. The case is preserved when lowercase characters are specified. See the Database Programming topic in the Information Center for more information on naming conventions for constraints.

PRNFILE
Specifies the parent file and qualifying library of a referential constraint. The file must be a physical file and it must allow a maximum of one member (MAXMBRS(1)).

The name of the parent file can be qualified by one of the following library values:

*LIBL: All libraries in the job's library list are searched until the first match is found.

*CURLIB: The current library for the job is searched. If no library is specified as the current library for the job, the QGPL library is used.

library-name: Specify the name of the library to be searched.

parent-file: Specify the name of the parent file of a referential constraint.

PRNKEY
Specifies the parent key, which is the definition of the access path on a parent file of a referential constraint. Only unique constraints or the primary key constraint of a parent file can be used to define a referential constraint. The parent key is one or more fields that exist in the file specified on the PRNFILE parameter.

*PRNFILE: The access path of the parent file is used when the access path is either a primary key constraint or a unique constraint.

field-name: Specify the name of the field for the constraint key you are defining. Each field name must exist in the file specified on the PRNFILE parameter. You can specify a maximum of 120 (but no duplicate) field names to define the parent key, where:

  • The fields can allow nulls.

  • The field names are of the object type *NAME and are a maximum length of 10.

  • The fields must be in ascending order.

  • The fields must match the type and length attributes of the fields specified for the foreign key.

  • The maximum number of bytes in a key is 2000 bytes (see the Database Programming topic in the Information Center for more information on this limitation).


DLTRULE
Specifies the delete rule for a referential constraint between a parent file and dependent file. The delete rule restricts or defines the effect of deleting a record in both the parent file and the dependent file.

*NOACTION: The no action delete rule is used. The delete rule is enforced at the end of the delete request. The following are attributes of the no action delete rule:

  1. Deleting a record in a parent file is permitted (not restricted) if data for a non-null parent key does not match data for a foreign key.

  2. Deleting a record in a parent file is restricted (does not occur) if data for a non-null parent key matches data for a foreign key.

*RESTRICT: The restrict delete rule is used. The delete rule is enforced at the beginning of the delete request. The following are attributes of the restrict delete rule:

  1. Deleting a record in a parent file is permitted if data for a non-null parent key does not match data for a foreign key.

  2. Deleting a record in a parent file is restricted if data for a non-null parent key matches data for a foreign key.

*CASCADE: The cascade delete rule is used. Deleting a record in a parent file causes matching records in the dependent file to be deleted when data for a non-null parent key matches data for a foreign key.

*SETNULL: The set null delete rule is used. Deleting a record in a parent file updates matching records in a dependent file if data for a non-null parent key matches data for a foreign key. If the matching foreign key field is null-capable, the value is set to null. If the matching foreign key field is not null-capable, the field is not updated.

Note: To use this rule, a minimum of one field in the foreign key access path must be null-capable.

*SETDFT: The set default delete rule is used. The following are attributes of the set default delete rule:

  1. Deleting a record in the parent file updates matching records in the dependent file when data for a non-null parent key matches data for a foreign key. The matching foreign key values are set to the default value as defined by the default.

  2. The default foreign key value must match the corresponding parent key value when there are no null-capable fields.


UPDRULE
Specifies the update rule for a referential constraint between a parent file and dependent file. The update rule restricts or defines the effect of updating a record in both the parent file and the dependent file.

*NOACTION: The no action update rule is used. The update rule is enforced at the end of the update request. The following are attributes of the no action update rule:

  1. Update a record in a parent file is permitted (not restricted) if data for a non-null parent key does not match data for a foreign key.

  2. Update a record in a parent file is restricted (does not occur) if data for a non-null parent key matches data for a foreign key.

*RESTRICT: The restrict update rule is used. The update rule is enforced at the beginning of the update request. The following are attributes of the restrict update rule:

  1. Update a record in a parent file is permitted if data for a non-null parent key does not match data for a foreign key.

  2. Update a record in a parent file is restricted if data for a non-null parent key matches data for a foreign key.


CHKCST
Specifies a check constraint expression that results in a check constraint. This parameter is only valid for TYPE(*CHKCST).

A check constraint is a validity check placed on fields of a database physical file. The data being inserted or updated into fields with a check constraint must meet the validity check prior to the insert or update of a record. If not all of the validity checks are met, then the write or update request is not performed and a message will be signaled back to the program of the requesting function indicating a check constraint violation.

The check constraint expression has the same syntax as used for SQL. The corresponding SQL term for check constraint expression is a check constraint search condition. For syntax rules, refer to the SQL Reference topic in the Information Center.

Examples for ADDPFCST

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.

Error messages for ADDPFCST

*ESCAPE Messages

CPF32B0
Constraint cannot be added to file &1.
CPF32B7
&3 constraint(s) added to file &1 but constraint(s) in error.