Add PF Constraint (ADDPFCST)

Where allowed to run: All environments (*ALL)
Threadsafe: Conditional

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.

Top

Parameters

Keyword	Description	Choices	Notes
FILE	File	Qualified object name	Required, Positional 1
	Qualifier 1: File	Name
	Qualifier 2: Library	Name, LIBL, CURLIB
TYPE	Constraint type	REFCST, UNQCST, PRIKEY, CHKCST	Required, Positional 2
KEY	Constraint key	Values (up to 120 repetitions): Name	Optional, Positional 3
CST	Constraint name	Character value, *GEN	Optional
PRNFILE	Parent file	Qualified object name	Optional
	Qualifier 1: Parent file	Name
	Qualifier 2: Library	Name, LIBL, CURLIB
PRNKEY	Parent key	Single values: *PRNFILE Other values (up to 120 repetitions): Name	Optional
DLTRULE	Delete rule	CASCADE, NOACTION, SETDFT, SETNULL, *RESTRICT	Optional
UPDRULE	Update rule	NOACTION, RESTRICT	Optional
CHKCST	Check constraint	Character value	Optional

Top

File (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.

This is a required parameter.

Qualifier 1: File

name: Specify the name of the physical file.

Qualifier 2: Library

*LIBL: All libraries in the library list for the current thread 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.
name: Specify the name of the library to be searched.

Top

Constraint type (TYPE)

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

This is a required parameter.

*REFCST

A referential constraint is being added.

Notes:

Referential constraints cannot span multiple ASPs (auxiliary storage pools).
Referential constraints cannot be added while either the parent or the dependent file is open.
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 category in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/ 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.

Top

Constraint key (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 referential and unique constraints, the fields specified can allow nulls (ALWNULL). For primary key constraints, the fields specified cannot allow nulls.

*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.
*CHKCST: The constraint key (KEY) parameter does not apply for check constraints. Check constraints use the CHKCST parameter to specify a check constraint expression.

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 32768 bytes (see the Database category in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/ for more information on this limitation).

Top

Constraint name (CST)

Specifies the name of the constraint being added.

*GEN: The system generates a constraint name.
character-value: 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 258 characters with quotation mark (") delimiters.
Note: The case is preserved when lowercase characters are specified.

Top

Parent file (PRNFILE)

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

Qualifier 1: Parent file

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

Qualifier 2: Library

*LIBL: All libraries in the library list for the current thread 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.
name: Specify the name of the library to be searched.

Top

Parent key (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.

Single values

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

Other values (up to 120 repetitions)

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 32768 bytes (see the Database category in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/ for more information on this limitation).

Top

Delete rule (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:

Deleting a record in parent file is permitted (not restricted) if data for a non-null parent key does not match data for a foreign key.
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:

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.
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:

Deleting a record in the parent file updates matching records in the dependent file when a 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.
The default foreign key value must match the corresponding parent key value when there are no null-capable fields.

Top

Update rule (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:

Updating a record in parent file is permitted (not restricted) if data for a non-null parent key does not match data for a foreign key.
Updating 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:

Updating a record in a parent file is permitted if data for a non-null parent key does not match data for a foreign key.
Updating a record in a parent file is restricted if data for a non-null parent key matches data for a foreign key.

Top

Check constraint (CHKCST)

Specifies the check constraint expression that is being added.

character-value: Specify the check constraint expression. The expression has the same syntax as used for SQL check conditions. See the the DB2 for i SQL reference topic collection in the IBM i Information Center at http://www.ibm.com/systems/i/infocenter/ for more information on syntax rules.

Top

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.

Top

Error messages

*ESCAPE Messages

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

Top