Referential Integrity

Referential Integrity

While single-value constraints show that at most one value exists in a given role, a referential integrity constraint asserts that exactly one value exists in that role. We could see a constraint that an attribute have a non-null, single value as a kind of referential integrity requirement, but "referential integrity" is more frequently used to refer to relationships among entity sets.

Let us consider the many-one relationship Owns from Movies to Studios in "Entity-Relationship Diagrams" figure. The many-one requirement simply says that no movie can be owned by more than one studio. It does not say that a movie must surely be owned by a studio, or that, even if it is owned by some studio, that the studio must be present in the Studios entity set, as stored in our database.

A referential integrity constraint on relationship Owns would need that for each movie, the owning studio (the entity "referenced" by the relationship for this movie) must exist in our database. There are various ways this constraint could be enforced.

1. We could forbid the deletion of a referenced entity (a studio in our example). That is, we could not delete a studio from the database unless it did not own any movies.

2. We could require that if a referenced entity is deleted, then all entities that reference it are deleted as well. In our example, this approach would require that if we delete a studio, we also delete from the database all movies owned by that studio.

In addition to one of these policies about deletion, we require that when a movie entity is inserted into the database, it is given an existing studio entity to which it is attached by relationship Owns. Moreover, if the value of that relationship changes, then the new value must also be an existing Studios entity. Enforcing these policies to assure referential integrity of a relationship is a matter for the implementation of the database, and we shall not talk about the details here.