You can enable or disable a referential constraint using the Change Physical File Constraint (CHGPFCST) command or iSeries™ Navigator.
To enable or disable a referential constraint relationship, use the CHGPFCST command. You must specify the dependent file when changing a referential constraint; you cannot disable or enable a constraint by specifying the parent file.
You can also enable or disable a referential constraint using iSeries Navigator.
You must have a minimum of object management authority (or ALTER privilege) to the dependent file in order to enable or disable a constraint.
When the system enables or disables a constraint, it locks the parent and the dependent files, both members, and both access paths. It removes the locks when the enable or disable operation is complete.
Attempting to enable an enabled constraint or disable a disabled constraint does nothing but cause the issuance of an informational message.
An established/disabled or check pending constraint relationship can be enabled. The enabling causes the system to verify the constraint again. If verification finds mismatches between the parent and the foreign keys, the constraint is marked as check pending.
Disabling a constraint relationship allows all file input/output (I/O) operations for both the parent and the dependent files, if the user has the correct authority. The entire infrastructure of the constraint remains. The parent key and the foreign key access paths are still maintained. However, there is no referential enforcement that is performed for the two files in the disabled relationship. All remaining enabled constraints are still enforced.
Disabling a constraint can allow file I/O operations in performance-critical situations to run faster. Always consider the trade-off in this kind of a situation. The file data can become referentially not valid. When the constraint is enabled, depending on the file size, the system will take time to re-verify the referential constraint relationship.
Users and applications must be cautious when modifying files with a constraint relationship in the established and disabled state. Relationships can be violated and not detected until the constraint is enabled again.
The Allocate Object (ALCOBJ) command can allocate (lock) files while a constraint relationship is disabled. This allocation prevents others from changing the files while this referential constraint relationship is disabled. Specify a lock state of Exclusive read (*EXCLRD) on the command so other users can read the files. After the constraint is enabled again, the Deallocate Object (DLCOBJ) command unlocks the files.
When you enable or disable multiple constraints, they are processed sequentially. If a constraint cannot be modified, you receive a diagnostic message, and the function proceeds to the next constraint in the list. When all constraints have been processed, you receive a completion message listing the number of constraints modified.
Related concepts
Getting started with iSeries Navigator SQL programming