Referential Integrity for Foreign Keys in Views

After you set up a Referential Integrity definition for a foreign key in a table, you do not need to set up another Referential Integrity definition for the same foreign key in a local view based on the table. For a foreign key in a local view, you normally only need to set up Field Validation and Picklist Help definitions.

However, you would want to set up Referential Integrity for a foreign key in a view (a) if are using a remote view and you want VPME to enforce RI on remote data or (b) if you needed to speed up RI processing on a large table.

Improving RI Performance with a View

When a table's primary key changes or is deleted, VPM's RI routine must find every matching foreign key that has been linked to the table's primary key through an RI definition and take the action specified by the Update and Delete rules chosen for the foreign key. As you can imagine, VPM's RI routine can take a significant amount of time if there are many related foreign keys or if the tables containing the foreign keys are large (i.e., they contain millions of records).

Large tables with foreign keys degrade RI performance because the table must be searched to find all records where the foreign key value matches the primary key value that has changed or been deleted. You can use a parameterized view to speed this process up. You can speed up RI performance when dealing with large tables by taking the following steps:

1.   Bypass RI for Table: Check the Bypass RI check box for the foreign key in the table. You will probably still want to set up an RI definition for the table to take advantage of VPM's Related Forms functionality. Of course, if you don't set up an RI definition for the foreign key in the table, you don't need to check the Bypass RI check box.

2.   Create RI for View: Create an RI definition for a parameterized view based on the table. The view must have a Filter on the foreign key with a parameter (variable) named "ParentPKValue" or RI<fieldname>.

If you take these steps, VPME will use the view for RI processing, instead of the table, but any foreign key changes or deletions will still be applied to the records in the table.


Table, View and CursorAdapter Relations