Referential Integrity in Database: Short Notes (Comprehensive Course Section)

Learning Goal

In relational databases, referential integrity ensures that relationships expressed by foreign keys always remain consistent: a child row cannot reference a non-existent parent row. This prevents “orphan” records and maintains correctness of joins and application logic.
Core mechanisms include foreign key constraints, update/delete rules (e.g., CASCADE), and careful modeling of cardinality in ER design.

Key terms (for this section): referential integrity foreign key constraint orphan record

Note: Exact SQL syntax and behavior can differ slightly across DBMSs (e.g., MySQL vs PostgreSQL vs SQL Server), but the underlying concept is consistent.

1) What Referential Integrity Means

Referential integrity is the property that every foreign key value in a child table must match an existing key value in the parent table (or be allowed to be NULL, depending on schema design). When the DBMS enforces this via constraints, it guarantees that the database cannot reach a state where relationships are broken.

Common problems it prevents

• Orphan records: child rows pointing to missing parent rows.
• Inconsistent updates: changing or deleting parent keys without handling dependent rows.
• Incorrect reporting: joins producing incorrect results.

Key terms: parent table child table primary key unique key

2) Foreign Keys: The Enforcer

A foreign key constraint links a column(s) in the child table to a primary key (or unique key) in the parent table. When enabled, the DBMS validates operations such as:

• inserting a child row
• updating a child row’s foreign key
• deleting (or updating) a parent key referenced by child rows

Relationship coverage

• One-to-many (typical): one parent row with many child rows
• One-to-one (often modeled with UNIQUE + FK)
• Many-to-many: done via an intersection table with two FKs

Key terms: referenced key matching update anomaly delete anomaly

3) Update/Delete Actions (ON DELETE / ON UPDATE)

When parent keys change or are deleted, referential integrity requires a defined policy for dependent child rows. SQL provides actions such as:

• CASCADE
• SET NULL
• RESTRICT
• NO ACTION

These behaviors can be visualized as follows:

Design implications

• Use CASCADE when the child row has no meaning without the parent.
• Use SET NULL when the child can exist independently after the parent disappears.
• Use RESTRICT/NO ACTION when deletion/updating must be handled explicitly by the application or business rules.

Key terms: CASCADE SET NULL RESTRICT NO ACTION

4) NULLs and Optional Relationships

A foreign key can often be NULL to indicate “no parent chosen” (optional relationship). Referential integrity typically means:

• Non-NULL child foreign keys must match a parent key.
• NULL foreign keys are not required to match any parent.

This distinction is crucial when designing schemas where relationships may be unknown or not applicable.

Key terms: nullable foreign key optional relationship NOT NULL

5) Why Referential Integrity Matters (Beyond “Correctness”)

Referential integrity affects more than storage consistency:

Query correctness

If integrity is enforced, joins between parent and child tables behave predictably because referenced rows exist.

Data quality and maintainability

It reduces “data cleaning” efforts caused by orphaned rows.

Application safety

DBMS-enforced constraints shift correctness responsibility from scattered application code to the database engine.

Key terms: join consistency transaction

6) Common Pitfalls (Quick Notes)

• Missing indexes on foreign key columns can slow enforcement and joins.
• Cascades that are too aggressive can cause large unintended deletions/updates.
• Inconsistent schema design: referencing non-key columns or mismatched data types.
• Disabling constraints during bulk loads without re-validating can create integrity gaps.

Key terms: index bulk load migration