Referential Integrity

Before we describe the VPM Enterprise Referential validation type to you, we will define “referential integrity” for you.

C. J. Date (An Introduction to Database Systems, Volume I, Addison-Wesley Publishing Company, Reading, MA, 1987) defined the following referential integrity rule of the relational model:

 

Referential Integrity

If a table, view or CursorAdapter includes a foreign key (FK) matching the primary key (PK) of some other table, view or CursorAdapter, every value of FK must be either:

a) Equal to the value of PK in some record in the other table, view or CursorAdapter, or

b) Entirely null.

 

 

Primary Key

The field or combination of fields that uniquely identifies each record in a table.

 

 

Foreign Key

A field or combination of fields in one table, view or CursorAdapter whose values are required to match those of the primary key of another table.

 

See Referential Validation to learn how to set up Referential Validation for a field.

The referential integrity rule is concerned with maintaining valid references between primary keys and related foreign keys. If you use the VPM Enterprise Referential validation type to validate foreign keys, your VPM Enterprise applications will adhere to the referential integrity rule for the situations in which it might be violated.

Tip - Use VPME Referential Integrity Engine Instead of Visual FoxPro Referential Integrity Builder: While you can use the Visual FoxPro Referential Integrity Builder to set up referential integrity for your application’s tables, we suggest you don’t. Use the VPM Enterprise referential integrity engine instead. VPM’s referential integrity engine provides capabilities Visual FoxPro doesn’t, such as: support for compound keys and free tables.

Preventing Violation of the RI Rule

The referential integrity rule can be violated when a table, view or CursorAdapter record is inserted, updated or deleted. Here’s how you prevent violation of the referential integrity rule when you set up Referential validation for foreign key fields:

Inserts - Entry or Change of Foreign Key Fields

When foreign key fields are entered or changed on data entry screens, the foreign key fields (a) must match a primary key in the table, view or CursorAdapter to which the foreign key is related or (b) be left blank (if you permit blank foreign keys).

Action:  If you use the Referential validation type to validate a foreign key, it won’t be possible to enter a foreign key that doesn’t match a primary key in the related table, view or CursorAdapter. If you select the On validation option, the foreign key cannot be null. If you allow null foreign keys, select the On/Empty/Null option. It is not normally desirable to allow a null foreign key.

Updates - Updating the Primary Key

If a user updates (or changes) a primary key that another table, view or CursorAdapter uses as a foreign key, the foreign key loses its reference to a primary key and the referential integrity rule is violated.

Action:  When you set up Referential validation for a foreign key, select one of the following four Update Rules on the Integrity dialog for handling updates.

§  Restrict: Allow the primary key update only if there are no records with matching foreign keys.

§  Set Empty: Set the foreign key values in all matching records to empty.

§  Cascade: Automatically update the matching foreign key values to the new value of the primary key.

§  No Change: Allow the primary key update without updating matching foreign keys.

Deletes - Deletion of Primary Key

If a user deletes a record containing a primary key that another table, view or CursorAdapter uses as a foreign key, the foreign key loses its reference to a primary key and the referential integrity rule is violated.

Action:  When you set up Referential validation for a foreign key, select one of the following four Delete Rules on the Integrity dialog for handling deletes.

·       Restrict: Allow the deletion only if there are no records with a foreign key matching the primary key about to be deleted.

·       Set Empty: Set the foreign key values in all matching records to empty.

·       Cascade: Automatically delete all records in all tables with the matching foreign key.

·       No Change: Allow the deletion without regard to matching foreign keys.

VPM Enterprise Meets ANSI Standards: The American National Standards Institute (ANSI) has established standards for the implementation of referential integrity in relational database systems. VPM Enterprise provides one of the few implementations of referential integrity that meets the ANSI standards.

 

Tip: When a change or deletion fails because of the Update or Delete Restrict rule, a message is displayed that identifies the table that contains the record that caused the update or deletion to fail.

More:

Referential Validation