Home | Previous Page | Next Page   Appendix C. Application Tuning Examples > Improving Large Data Updates >

Verifying Foreign-Key Constraints

A foreign-key constraint for a column in a child table ensures that the parent table contains a row with a value that corresponds to the row with the foreign-key constraint. This constraint must be checked for each row inserted into a child table. The traditional way to check is to have an index on the primary key of the parent table and do an index lookup for each row inserted into the child table. In any environment that requires bulk inserts into the child table, this method is not efficient.

To take advantage of fast parallel processing, use a three-table solution, which does not require an index on any table. The three tables are the parent table, P; the child table, C; and an empty table, E, which duplicates the structure and fragmentation of the child table. For the best parallel performance, use an outer join between the child table and the parent table to select the rows in the child table that violate the foreign key constraint and insert these rows into the empty table, as the following example shows:

INSERT INTO E
   SELECT P.fk, C.c2
   FROM OUTER C, P
      WHERE P.fk = C.pk
   GROUP BY 1,2
   HAVING C.c2 IS NULL;

Then use the rows selected into the empty table to delete the noncompliant rows in the permanent child table, as the following example shows:

DELETE FROM C
     WHERE EXISTS
     (SELECT 1 FROM E WHERE C.fk =E.fk);
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]