A parent file must be a physical file with a maximum of one member.
You can create a new file or use an existing file when you define 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, you must 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:
- Use the Create Physical File (CRTPF) command to create the file.
- 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.
Note: 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,
you must 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.