Defining the parent file in a referential constraint

 

A parent file must be a physical file with a maximum of one member. You can either create a parent file or use an existing file as the parent file.

The concept of a parent key applies only in terms of a referential constraint. When a referential constraint is added to the dependent file, a parent key is required for the parent file. To prepare for this, first add either a primary key constraint or a unique constraint to the parent file with the appropriate set of fields for the key. When the referential constraint is added, a search is conducted of unique constraints (and primary key) for a match. If a match is found, then the access path of the constraint is used as the parent key in the referential constraint relationship. To create a new physical file as a parent file, follow these steps:

1. Use the Create Physical File (CRTPF) command to create the file.

2. Use the Add Physical File Constraint (ADDPFCST) command to either add a primary key constraint or a unique constraint. The primary key can be null-capable, but the system creates an implicit check constraint to prevent the insertion of null values in the field.

You can use the SQL CREATE TABLE statement to perform the preceding steps with one step.

To use an existing file as a parent file, choose from the following options:

• You can add a primary key constraint to a file with the ADDPFCST command. Specify *PRIKEY for the TYPE parameter. You must also specify the key field or fields with the KEY parameter.

  If a primary key constraint already exists for the file, the ADDPFCST command with TYPE(*PRIKEY) will fail because a file can have only one primary key. If you want a different primary key constraint, first remove the existing primary key constraint with the Remove Physical File Constraint (RMVPFCST) command. Then you can add a new primary key constraint.

• You can add a unique constraint to a file with the ADDPFCST command. Specify *UNQCST for the TYPE parameter. You must also specify the key field or fields with the KEY parameter. You can also add a unique constraint with the Structured Query Language (SQL) ALTER TABLE statement.

  If the parent file does not have a primary key or unique constraint that can be used as the parent key, the system will attempt to automatically add a primary key constraint when adding a referential constraint.

  If the parent file has a uniquely keyed access path, where the access path fields match the foreign key's fields (both for the number of fields and matching attributes), then a primary key constraint will be implicitly added to the parent file. This will become the parent key for the referential constraint.

  If the parent file is arrival sequence access path, then if the fields specified for the parent key match the foreign key's fields (matching attributes), then a primary key constraint will be implicitly added to the parent file. This will become the parent key for the referential constraint.

 

What to do when you cannot define a parent key

For an existing file with a primary key or unique constraint, if neither constraint will suffice as the parent key, there are options you can turn to. You can take either of the following actions when you cannot define a parent key.

• Delete the file and create it again with the appropriate keys.

• Add a unique or primary key constraint to the created file.

 

Parent topic:

Adding referential constraints

 

Related reference

Create Physical File (CRTPF) command
Add Physical File Constraint (ADDPFCST) command
Remove Physical File Constraint (RMVPFCST) command