*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.

### Tags

- referential integrity
- entity
- tuple
- relational algebra
- attribute
- Queries in PSM
- Using Shared Variables
- System Aspects of SQL
- Instead-Of Triggers
- Triggers in SQL
- Modification of Constraints
- Constraints on Attributes and Tuples
- Deferring the Checking of Constraints
- Maintaining Referential Integrity
- Declaring Foreign-Key Constraints
- Interpreting Queries Involving Views
- Full-Relation Operations
- Natural Joins / Outerjoins
- Subqueries in FROM Clauses
- Correlated Subqueries
- Conditions Involving Tuples
- Subqueries
- Union, Intersection, and Difference of Queries
- Interpreting Multirelation Queries
- Queries Involving More Than One Relation
- The Truth-Value UNKNOWN
- Selection in SQL
- The Database Language SQL
- Additional Constraint Examples
- Constraints on Relations
- Outerjoins
- Extending the Projection Operator
- Extended Operators of Relational Algebra
- Selection on Bags / Product of Bags / Joins of Bags
- Relational Operations on Bags
- A Linear Notation for Algebraic Expressions
- Dependent and Independent Operations
- Renaming
- Combining Operations to Form Queries
- Natural Joins / Theta-Joins
- Set Operations on Relations
- An Algebra of Relational Operations
- Object-Oriented Versus Object-Relational
- References
- Representing Set-Valued Attributes
- From ODL Designs to Relational Designs
- Additional ODL Concepts
- From E/R Diagrams to Relational Designs
- Referential Integrity in E/R Diagrams
- Avoiding Redundancy
- Instances of an E/R Diagram
- Database Programming
- The Query Processor