Referential Integrity and the Semantics of Foreign Key Actions

A foreign key is a constraint on two relations at once: a set of attributes in the referencing relation must always hold values that appear as a key in the referenced relation. Nothing in the definition says what to do when a row in the referenced relation is deleted or its key is changed, yet that is the case implementations must handle, and the handling is where the semantics become subtle. The referential action clauses are not convenience options; each one is a different answer to the question of how a constraint violation is resolved, and each has a distinct failure mode.

The assertion the constraint makes

Given relations R (referencing) and S (referenced), with attribute sets FK ⊆ attrs(R) and K ⊆ attrs(S) where K is a key of S, the constraint asserts:

For every tuple t in R, either t[FK] contains a null in at least one of its attributes, or there exists a tuple u in S with u[K] = t[FK].

That disjunction is the whole specification, and both halves of it are consequential. The first half is the match condition, which decides whether a row is exempt from the reference requirement. The second half is the reference condition, which decides what counts as a valid target.

K must be a key — in SQL terms, a PRIMARY KEY or a UNIQUE constraint with the referenced columns. A reference to a non-unique column set is rejected by the standard, because a set of values that appears multiple times in S does not identify a tuple. Implementations may accept it in restricted forms, but the resulting constraint is not a foreign key in the model’s sense and cannot support the guarantees the model derives from it.

The constraint is checked as a statement about the committed state of the database, not about individual operations. A transaction may pass through a state in which a referencing row has no target, provided the constraint is satisfied by the time the transaction commits. This is why a constraint declared DEFERRABLE INITIALLY DEFERRED permits a delete-then-insert sequence that would fail if checked per statement: the intermediate state is never observable to anyone else.

Match types decide when a null key is exempt

SQL defines three match types, and the difference between them is a detail of three-valued logic that changes which rows are checked at all — the logic treated in nulls and the constraints they break.

MATCH SIMPLE is the default. A composite foreign key is exempt from the reference requirement if any of its attributes is null. A row with (customer_id, region) = (42, NULL) passes the constraint without any matching row in the referenced table, even if (42, 'EU') exists and (42, NULL) is meaningless.

MATCH FULL requires that a composite key be entirely null or entirely non-null. A partially null key violates the constraint outright, whether or not a target exists. This is the setting that matches the intuition of “either this row refers to nothing, or it refers to something complete.”

MATCH PARTIAL would require that any non-null subset of the key match a target on those columns. The standard defines it; as of current implementations it is generally unimplemented, and attempting to declare it raises an error in the major open-source systems. The concept is coherent but expensive to enforce, since it needs an index on every subset of the key columns.

The choice matters most in schemas with nullable composite keys. Under MATCH SIMPLE, a null in one column silently disables the constraint for that row, which is the desired behaviour when the key encodes an optional relationship and a correctness hazard when it does not.

What happens when the target disappears

Deleting a referenced row, or updating its key, invalidates the reference from every dependent row. The standard offers five responses, and they differ in which side of the relationship absorbs the change.

NO ACTION defers the check to the end of the statement, or to the end of the transaction if the constraint is deferred. If a dependent row still references the removed key at that point, the operation fails and the entire statement is rolled back. The distinction from RESTRICT is precisely the timing: NO ACTION can be satisfied by a later statement in the same transaction that re-establishes the reference, whereas RESTRICT cannot.

RESTRICT checks immediately, before the operation proceeds. A delete that would orphan a dependent row is rejected even if a subsequent statement in the same transaction would have repointed the dependent row. RESTRICT is the correct choice when the constraint encodes an invariant that must hold at every instant.

CASCADE deletes the dependent rows along with the target, or propagates the new key value to them. This is the only action that resolves the violation without failing, and it is also the one that can remove far more data than the statement appears to specify. A delete on a parent row can cascade through several levels of a self-referencing hierarchy, and the total number of rows removed is the size of the subtree, not one.

SET NULL writes nulls into the referencing columns. This requires those columns to be nullable, and under MATCH SIMPLE it converts the dependent row into one that is exempt from the constraint. Under MATCH FULL a composite key cannot be partially nulled, so SET NULL is only valid there if the entire key is nulled.

SET DEFAULT writes the declared default values into the referencing columns. The default must itself satisfy the constraint, or the operation fails with a violation reported against the default rather than against the original delete — a failure mode that is confusing to diagnose because the reported constraint is not the one the statement touched.

CREATE TABLE invoice (
  id          bigint PRIMARY KEY,
  customer_id bigint NOT NULL,
  issued_on   date NOT NULL
);

CREATE TABLE payment (
  id         bigint PRIMARY KEY,
  invoice_id bigint REFERENCES invoice (id)
             ON DELETE RESTRICT
             ON UPDATE CASCADE
);

The asymmetry in that declaration is deliberate and common: payments should not vanish when an invoice is deleted, but an invoice renumbering should carry through to its payments. ON DELETE RESTRICT makes the deletion fail loudly instead of destroying financial records, while ON UPDATE CASCADE keeps the reference valid if the key is corrected.

Cost and locking consequences

Every foreign key implies a lookup: on insert into R, the system must verify that a matching key exists in S. Without an index on the referenced columns this is a scan of S. SQL requires the referenced columns to be a key, and key constraints are normally backed by a unique index, so the check is an index probe — a B-tree descent of depth log_B |S| for a fanout B in the hundreds, so three or four page accesses on a table of a million rows, most of them cached.

The referencing side is the one that is often left unindexed, and the omission has consequences beyond check performance. Under ON DELETE CASCADE or ON DELETE SET NULL, the system must find every dependent row; without an index on R(FK) that is a full scan of R per deleted parent row. Under RESTRICT, the same scan is needed to determine that no dependent row exists. A delete of many parents therefore becomes quadratic in the absence of the index. The index on the referencing columns is not required by the standard and is not created automatically by most systems; it is a schema decision with a direct effect on delete cost.

Locking is the second cost. Checking a reference takes a shared lock on the target row so it cannot be deleted concurrently, and inserting into the referencing table therefore contends with deletes on the referenced table. In a workload with a hot parent row — a default category, a singleton configuration record — every insert into the child table serializes on the same lock. The alternative is to drop the constraint and enforce the invariant in application code, which trades a correctness guarantee for concurrency, and is a trade that should be made explicitly rather than by accident.

Cascades also interact with triggers and with statement-level timing. A cascading delete fires delete triggers on the dependent table, which may cascade further, and the recursion depth is bounded only by the depth of the data. Systems impose a limit — implementations differ, and the limit is not part of the standard — and exceeding it aborts the statement. A cyclic reference graph with ON DELETE CASCADE on both directions is the pathological case: it cannot be resolved by cascading and must be broken by a nullable key, a deferred constraint, or an explicit ordering of deletes in application logic.

Why the constraint belongs in the schema

Enforcing references in application code is possible and is common in systems that shard across databases, where a cross-shard foreign key cannot be checked by the storage engine at all. The cost of moving the check upward is that the invariant is no longer guaranteed: any write path that bypasses the application — a bulk load, an administrative script, a replication stream applying changes out of order — can violate it, and no subsequent query can distinguish the resulting dangling reference from a legitimate one.

The relational argument for keeping the constraint in the schema is that it makes the invariant part of the database’s value, checkable by any reader. A dangling reference is not a state the model permits, so a system that can produce one has a weaker guarantee than the model offers. The same reasoning applies to the other integrity constraints — domain constraints, NOT NULL, CHECK — and to the uniqueness requirement that makes the reference meaningful in the first place, which is derived from the key structure discussed in relations as sets and duplicate elimination.

Where references cross a boundary that cannot be enforced — separate databases, separate services — the standard practical answer is to keep the reference as a value without a constraint, and to accept that the invariant is maintained by a process rather than by the engine. The important part is to know which of the two situations applies, because the failure modes are different: an unenforced reference produces rows that point at nothing, while an enforced one produces statements that fail. Failing statements are easier to find.