Checking Data Integrity

In supporting our customers, we occasionally come across problems with data integrity in the GuardIEn database, specifically rows with orphan foreign keys.

An orphan foreign key is a foreign key value (for a simple or compound key) where the parent row does not exist.

For example, in the case of table PARENT has many CHILD, the key of PARENT exists as a foreign key in the CHILD table. If the CHILD FK_PARENT_CODE has a value where the PARENT row does not exist with the same code, then the CHILD foreign key is an orphan.

This situation can arise from errors in the RI trigger runtime routines, use of SQL to delete the parent rows without deleting / nullifying the child rows, incorrectly generated RI triggers or incorrectly implemented DBMS RI rules. In the case of GuardIEn, the most common causes are use of SQL to (incorrectly) load rows or delete rows, and on some platforms, issues with the RI trigger runtimes.

To help identify and fix these integrity issues, we have added a new genIE function that generates the SQL to firstly identify orphan rows and then to perform a cleanup.

We will be distributing the SQL to GuardIEn users so that they can check the integrity of the database, and the new function will be available in 8.1.4 so that customers can generate the SQL to check their own application databases.