Referential Integrity

Informally speaking, referential integrity concerns the relationship between the values in logically related tables. In the relational model, it means guaranteeing the logical consistency of the database by making sure that the values of a primary key and the foreign keys that point to it always match.

Chapter 2 explains that foreign key/primary key relationships are planned during the design of a database; they represent the logical relationships among data (although their presence in no way limits the possible access paths among the data). In considering referential integrity, the question is what the database system can do to guarantee the maintenance of matching values between foreign keys and the primary key to which they point (that is, that referential constraints are not violated). Chapter 3 introduces the REFERENCES and FOREIGN KEY constraints in the CREATE TABLE statement. These clauses ensure some referential integrity—they allow you to set up checks that prevent a foreign key from being added if it does not correspond to a primary key. However, this is only one issue in referential integrity.

For example, changing an author ID can present a problem because the alteration would destroy the connection between the authors, titles, and titleauthors tables. How do you deal with the primary key—changing the ID in the authors table or deleting or updating a publisher ID in the publishers table when books in the titles table still reference the old ID? One approach is to decide that primary keys should never be changed and, therefore, prevent these updates. The REFERENCES clause prevents this kind of change in the foreign key (titleauthors.au_id), and in the primary key (authors.au_id).

Another answer is to cascade the update or delete operation automatically to the matching foreign keys. For example, if a publisher's ID number changes, the system would change the matching IDs in titles.pub_id in exactly the same way, without intervention by the user.

A third possibility is to accept data modification operations to primary key values, even if they upset referential integrity, but first to change the matching foreign key values to NULL. (Of course, if the foreign key has been defined so as not to accept null values, this course of action is out.)

To summarize briefly, there are in general three possible responses to an attempt to delete or update a primary key to which a foreign key points:

• Restrict— the delete or update operation on the primary key is rejected unless there are no matching foreign key values.

• Cascade— the delete or update operation is automatically applied to the foreign keys whose values matched the “old” value of the deleted or updated primary key.

• Nullify— before the delete or update operation on the primary key is committed, the values of matching foreign keys are set to NULL.

Unfortunately, many SQL dialects do not provide mechanisms for controlling primary key referential integrity. In these cases, the closest you can come to guaranteeing referential integrity is to revoke all permissions for deleting and updating a primary key column.

However, as the importance of referential integrity becomes more and more widely recognized, vendors are beginning to address it with special procedures or triggers.