Referential integrity enforcement can present complex CMPFILDTA repair scenarios. Like triggers, a delete rule of “cascade”, “set null”, or “set default” can cause records in other tables to be modified or deleted as a result of a repair action. In other situations, a repair action may be prevented due to referential integrity constraints.
Consider the case where a foreign key is defined between a “department” table and an “employee” table. The referential integrity constraint requires that records in the employee table only be permitted if the department number of the employee record corresponds to a row in the department table with the same department number.
It will not be possible for CMPFILDTA repair processing to add a row to the employee table if the corresponding parent row is not present in the department table. Because of this, you should use CMPFILDTA to repair parent tables before using CMPFILDTA to repair dependent tables. Note that the order you specify the tables on the CMPFILDTA command is not necessarily the order in which they will be processed, so you must issue the command once for the parent table, and then again for the dependent table.
Repairing the parent department table first may present its own problems. If CMPFILDTA attempts to delete a row in the department table and the delete rule for the constraint is “restrict”, the row deletion may fail if the employee table still contains records corresponding to the department to be deleted. Such constraints should use a delete rule of “cascade”, “set null”, or “set default”. Otherwise, CMPFILDTA may not be able to make all repairs.
See the IBM Database Programming manual (SC41-5701) for more information on referential integrity.