Maintaining Referential Integrity

Maintaining Referential Integrity

We have studied how to declare a foreign key, and we learned that this declaration implies that any set of values for the attributes of the foreign key, none of which are NULL, must also appear in the corresponding attribute(s) of the referenced relation. But how is this constraint to be maintained in the face of modifications to the database? The database implementor may choose from among three alternatives.

The Default Policy: Reject Violating Modifications

SQL has a default policy that any modification violating the referential integrity constraint is rejected by the system. For example, look at "Declaring Foreign-Key Constraints" Example 1, where it is necessary that a presC# value in relation Studio also be a cert# value in MovieExec. The following actions will be rejected by the system (i.e., a run-time exception or error will be generated).

1.  We try to insert a new Studio tuple whose presC# value is not NULL and is not the cert# component of any MovieExec tuple. The insertion is rejected by the system, and the tuple is never inserted into Studio.

2.  We try to update a Studio tuple to change the presC# component to a non-NULL value that is not the cert# component of any MovieExec tuple. The update is rejected, and the tuple is unchanged.

3.  We try to delete a MovieExec tuple, and its cert# component appears as the presC# component of one or more Studio tuples. The deletion is rejected, and the tuple remains in MovieExec.

4.  We try to update a MovieExec tuple in a way that changes the cert# value, and the old cert# is the value of presC# of some movie studio. The system again rejects the change and leaves MovieExec as it was.

The Cascade Policy

There is another approach to handling deletions or updates to a referenced relation like MovieExec (i.e., the third and fourth types of modifications explained above), called the cascade policy. Intuitively, changes to the referenced attribute(s) are mimicked at the foreign key.

Under the cascade policy, when we delete the MovieExec tuple for the president of a studio, then to maintain referential integrity the system will delete the referencing tuple(s) from Studio. Updates are handled analogously. If we change the cert# for some movie executive from c1 to c2, and there was some Studio tuple with c1 as the value of its presC# component, then the system will also update this presC# component to have value c2.

The Set-Null Policy

Yet another approach to handling the problem is to change the presC# value from that of the deleted or updated studio president to NULL; this policy is called set-null.

These options may be chosen for deletes and updates, independently, and they are stated with the declaration of the foreign key. We declare them with ON DELETE or ON UPDATE followed by our choice of SET NULL or CASCADE.

Example 1 : Let us see how we might modify the declaration of

Studio(name, address, presC#)

in "Declaring Foreign-Key Constraints" Example 1 to specify the handling of deletes and updates in the

MovieExec(name, address, cert#, netWorth)

relation. Figure 1 takes the first of the CREATE TABLE statements in that example and expands it with ON DELETE and ON UPDATE clauses. Line (5) says that when we delete a MovieExec tuple, we set the presC# of any studio of which he or she was the president to NULL. Line (6) says that if we update the cert# component of a MovieExec tuple, then any tuples in Studio with the same value in the presC# component are changed likewise.

Choosing policies to preserve referential integrity

Note that in this example, the set-null policy makes more sense for deletes, while the cascade policy seems preferable for updates. We would expect that if, for example, a studio president retires, the studio will exist with a "null" president for a while. On the other hand, an update to the certificate number of a studio president is most likely a clerical change. The person continues to exist and to be the president of the studio, so we would like the presC# attribute in Studio to follow the change.

Dangling Tuples and Modification Policies