Foreign Keys and Referential Integrity

Motivation

Once we decompose a schema into multiple tables, we need a mechanism to express that a value in one table refers to a value in another. This is the role of the foreign key. Think of a foreign key as a sort of pointer — it does not store the target data, only the identifier that locates it.

Defining a foreign key

Let $R(\underline{Z}, Y)$ be a relational schema where $Z$ is the key of $R$. Let $S(W)$ be another relational schema, and suppose $Z \subseteq W$ — that is, the attributes of $Z$ appear as a subset of the attributes of $S$.

We say that $Z$ represents a foreign key in $S$ referencing $R$ if, for any valid instance of the database:

$\pi_Z(S) \subseteq \pi_Z(R)$

In words: every value of $Z$ that appears in table $S$ must also appear as a key value in table $R$. The projection of $S$ onto $Z$ is a subset of the projection of $R$ onto $Z$.

Referential integrity

A database is said to have referential integrity when all foreign key constraints are satisfied. The DBMS enforces these constraints: any INSERT or UPDATE that would cause a foreign key value to reference a nonexistent target is rejected.

Referential integrity is one of the two fundamental integrity constraints of the relational model. The other is entity integrity: no part of a primary key may be NULL.

Example: Has_Genre

Consider the many-to-many relationship between Movies and Genres:

Has_Genre(movie_id, genre_id)

This table has two foreign key constraints:

Foreign key	References	Meaning
movie_id	Movies(movie_id)	Every movie_id in Has_Genre must exist in Movies
genre_id	Genres(genre_id)	Every genre_id in Has_Genre must exist in Genres

Formally:

$\pi_{\text{movie\_id}}(\text{Has\_Genre}) \subseteq \pi_{\text{movie\_id}}(\text{Movies})$ $\pi_{\text{genre\_id}}(\text{Has\_Genre}) \subseteq \pi_{\text{genre\_id}}(\text{Genres})$

These constraints prevent dangling references. You cannot classify a nonexistent film into a genre. You cannot assign a nonexistent genre to a film.

Dangling references: a real-world analogy

A dangling reference is like a GP surgery’s database listing a patient’s doctor as “Dr. Yeti Goosecreature” when no such doctor exists on the staff table. The foreign key constraint catches this: the INSERT into the patient table would fail because doctor_id does not exist in the Doctors table. The system rejects the operation and demands correction.

Primary key vs. foreign key

	Primary key	Foreign key
Purpose	Identifies a row within its own table	References a row in another table
Uniqueness	Unique within its table	Not necessarily unique (many rows can reference the same target)
Nullability	Never NULL	May be NULL (if the relationship is optional)
Scope	Local to one table	Cross-table, linking two tables
Formal definition	Minimal superkey	$\pi_Z(S) \subseteq \pi_Z(R)$

Foreign keys in all-key tables

In an all-key relationship table such as Directed(movie_id, person_id), every column is both part of the primary key and a foreign key. The primary key constraint guarantees that no duplicate (movie, person) pairs exist; the foreign key constraints guarantee that both the movie and the person actually exist.

Multiple foreign keys to the same table

A table can have multiple foreign keys referencing the same target table. Consider a Transfers table in a banking database:

Transfers(id, from_account, to_account, amount)

Both from_account and to_account are foreign keys referencing Accounts(account_id). They play different roles but point to the same table.

ON DELETE and ON UPDATE actions

When a referenced row is deleted or its key is updated, the DBMS must decide what happens to the referencing rows. The SQL standard defines several actions:

Action	Behaviour
RESTRICT / NO ACTION	Reject the deletion/update if any referencing rows exist
CASCADE	Delete/update the referencing rows automatically
SET NULL	Set the foreign key column to NULL in referencing rows
SET DEFAULT	Set the foreign key column to its default value in referencing rows

The default in most DBMSs is RESTRICT. Choosing the right action depends on the semantics of the relationship.

Checking referential integrity

A DBMS checks referential integrity at the end of each statement (or at the end of each transaction, depending on the constraint mode). If a statement would leave the database in a state violating a foreign key constraint, the statement is rolled back and an error is returned to the client.

Summary

• A foreign key is a set of attributes in table $S$ that must match the key of some row in table $R$.
• Referential integrity means all foreign key constraints are satisfied.
• The DBMS enforces FK constraints by rejecting invalid inserts and updates.
• All-key tables consist solely of foreign keys that together form the primary key.
• ON DELETE and ON UPDATE actions control cascading behaviour when referenced rows change.