Referential Integrity Constraints

Referential Integrity Constraints

A common kind of constraint, called "referential integrity" in "The Modeling of Constraints", declares that a value appearing in one context also appears in another, related context. We saw referential integrity as a matter of relationships "making sense". That is, if an object or entity A is related to object or entity B, then B must really exist. For instance, in ODL terms, if a relationship in object A is represented physically by a pointer, then referential integrity of this relationship declares that the pointer must not be null and must point to a genuine object.

In the relational model, referential integrity constraints look somewhat different. If we have a value  in a tuple of one relation R, then because of our design intentions we may expect that  will appear in a specific component of some tuple of another relation S. An example will show how referential integrity in the relational model can be expressed in relational algebra.

Example 1: Let us think of our running movie database schema, particularly the two relations

Movie(title, year, length, inColor, studioName, producerC#)
MovieExec(name, address, cert#, netWorth)

We might reasonably assume that the producer of every movie would have to appear in the MovieExec relation. If not, there is something wrong, and we would at least want a system implementing a relational database to inform us that we had a movie with a producer of which the system had no knowledge.

To be more specific, the producerC# component of each Movie tuple must also appear in the cert# component of some MovieExec tuple. Since executives are uniquely identified by their certificate numbers, we would thus be assured that the movie's producer is found among the movie executives. We can express this constraint by the set-containment

The value of the expression on the left is the set of all certificate numbers appearing in producerC# components of Movie tuples. Similarly, the expression on the right's value is the set of all certificates in the cert# component of MovieExec tuples. Our constraint says that every certificate in the former set must also be in the latter set.

By the way, we could express the same constraint as an equality to the emptyset:

Example 2: We can likewise express a referential integrity constraint Where the "value" involved is represented by more than one attribute. For example, we may want to declare that any movie mentioned in the relation

StarsIn(movieTitle, movieYear, starName)

also appears in the relation

Movie(title, year, length, inColor, studioName, producerC#)

Movies are represented in both relations by title-year pairs, because we agreed that one of these attributes alone was not enough to identify a movie. The constraint

expresses this referential integrity constraint by comparing the title-year pairs produced by projecting both relations onto the appropriate lists of components.