Referential integrity considerations - assure_mimix - 10.0

Assure MIMIX Administrator Reference

Product type
Software
Portfolio
Integrate
Product family
Assure
Product
Assure MIMIX™ Software
Version
10.0
Language
English
Product name
Assure MIMIX
Title
Assure MIMIX Administrator Reference
Copyright
2023
First publish date
1999
ft:lastEdition
2024-05-07
ft:lastPublication
2024-05-07T13:36:02.962500

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.

Note: These repair issues can be avoided if target constraint management is an appropriate option for your environment. For more information, see Target constraint management

See the IBM Database Programming manual (SC41-5701) for more information on referential integrity.