Protect Your Data? (3)

Restrict, Null, Cascade

When a primary key is removed or changed while referencing foreign keys exist, a DBMS can provide several options for enforcing Referential Integrity. These options are known as Referential Integrity Effects. Different effects may be defined for removing a primary key (Delete Effect) and for changing a primary key (Update Effect). The relational model defines 3 effects:

Restrict - the change to the primary key is rejected; this is the most commonly supported effect.
Null - all referencing foreign keys are set to null.
Cascade - the change to the primary key is also performed (cascaded) on all referencing foreign keys.

Cascade effects are different for Delete and Update. For Delete, all referencing foreign key rows are deleted. For Update, all referencing foreign key values are changed to the new primary key value (perserving Referential Integrity).

Note that the cascade effects can be wide-ranging. When the primary keys of rows deleted from the foreign key table are themselves referenced by other foreign keys, deletes may be cascaded to another table. When the foreign key shares columns with its own primary key, updates may also cascade further.

Many RDBMSs that do support referential integrity do not support the full complement of Referential Integrity Effects. This causes severe problems for users of those systems. Each of the 3 Referential Integrity Effexts in the relational model reflects real world relationships and policies. Without appropriate capabilities, users are forced to use application code for referential integrity enforcement, circumventing system control.

FirstSQL is one of the very few RDBMSs that supports the complete set of Entity and Referential Integrity Properties, Rules and Effects. FirstSQL ensures complete system control of Referential Integrity.

The next section describes FirstSQL support for Extended Referential Integrity.

Return to Contents Page: Protect Your Data? Return to Can your Database do this?