PostgreSQL Foreign Keys Turn Reference Checks Into Row Locks

A PostgreSQL insert into a child table can block a concurrent transaction that tries to delete the referenced parent row, even though the two statements modify different tables. Foreign key enforcement is not only a value lookup. The database must also prevent the referenced key from disappearing before the referencing transaction reaches its boundary.

That requirement creates a concurrency relationship between child writes and parent-row changes. The relationship is narrower than a general parent-row write lock: PostgreSQL has a row-lock mode specifically compatible with updates that leave key columns intact.

Referential validity spans concurrent transactions

Consider a conventional parent-child schema:

CREATE TABLE account (
    account_id bigint PRIMARY KEY,
    display_name text NOT NULL
);

CREATE TABLE invoice (
    invoice_id bigint PRIMARY KEY,
    account_id bigint NOT NULL
        REFERENCES account(account_id)
);

Suppose transaction A inserts an invoice that references account 42. PostgreSQL must establish that the referenced key exists. A simple snapshot lookup would not be sufficient by itself. If transaction B could delete account 42 immediately after that lookup while A remained open, both transactions could commit and leave a dangling reference.

Foreign key enforcement therefore coordinates with row locking. A check of the referenced row protects the key against conflicting deletion or key-changing operations for the relevant transaction interval. The resulting wait is a consequence of preserving the constraint under concurrency, not evidence that the child insert directly modifies the parent tuple.

Key-share locking separates identity from other state

PostgreSQL exposes FOR KEY SHARE as its weakest explicit row-lock mode. A key-shared lock blocks DELETE and updates that change key values covered by a qualifying unique index, while remaining compatible with FOR NO KEY UPDATE, FOR SHARE, and other FOR KEY SHARE locks.

This distinction permits concurrency that a stronger lock would reject. If transaction A establishes a reference to an account, transaction B can still change an unrelated attribute such as display_name when that update does not modify a referenced key:

UPDATE account
SET display_name = 'North Region'
WHERE account_id = 42;

An operation that removes the row has different semantics:

DELETE FROM account
WHERE account_id = 42;

The delete needs a conflicting row lock. If another transaction is protecting the referenced key while inserting or updating a child reference, the delete can wait until that transaction ends.

The same boundary applies to an update that changes a referenced key. PostgreSQL treats updates of columns associated with unique indexes usable for foreign keys more strongly than updates of unrelated columns. The lock distinction lets ordinary parent-row maintenance proceed without sacrificing referential integrity.

Waiting can occur far from the apparent write target

Application traces often describe the child insert and parent delete as independent operations because their target tables differ. The foreign key makes that description incomplete.

For example, transaction A may execute:

BEGIN;

INSERT INTO invoice (invoice_id, account_id)
VALUES (9001, 42);

Before A commits, transaction B may execute:

BEGIN;

DELETE FROM account
WHERE account_id = 42;

If account 42 is the row protected by A’s reference check, B cannot complete the delete until the conflict is resolved. If A commits, the foreign key remains and the delete must then satisfy the configured referential action. With the default NO ACTION, deletion cannot leave the committed invoice pointing at a missing account.

This means lock analysis based only on rows explicitly named as write targets can omit a real dependency edge. Constraint enforcement can introduce row locks in referenced relations.

Referential actions change the work, not the integrity requirement

ON DELETE and ON UPDATE clauses alter the action PostgreSQL takes when a referenced key changes. They do not remove the need to coordinate concurrent transactions.

With ON DELETE CASCADE, deleting a parent can delete matching child rows as part of the referential action. With ON DELETE SET NULL, PostgreSQL can update referencing columns when the schema permits null values. RESTRICT and NO ACTION reject changes that would violate the constraint, though their timing semantics differ when deferral is involved.

Each action changes the statements and locks that may be required during enforcement. The common invariant remains that a committed database state cannot contain a non-null foreign key value requiring a match when no qualifying referenced row exists.

Concurrency behavior must therefore be reasoned about from both directions: child writes protect references they establish, while parent changes trigger checks or actions over existing references.

Indexes shape lookup cost but do not erase locking semantics

The referenced columns of a foreign key must be backed by an appropriate uniqueness guarantee. That gives PostgreSQL an efficient path for locating the parent key and establishes which values form the referenced identity.

The referencing columns are different. PostgreSQL does not automatically create an index on the child columns merely because a foreign key exists. Parent deletion or key updates can need to search the referencing table for matching rows, and an index on those columns can materially change the access path for that work.

An index choice does not change the logical constraint. It changes the mechanics and potential duration of locating affected rows. Lock contention and lookup cost are related operationally, but they are separate properties: an efficient lookup cannot make a conflicting referential change legal.

Deadlocks can include constraint-created edges

Once foreign key checks participate in locking, they can also participate in a deadlock cycle.

Suppose one transaction modifies a parent and then writes a child reference, while another transaction touches related rows in the opposite order. Each transaction can hold a lock needed by the other. The visible SQL may make the cycle appear to involve unrelated statements until the implicit constraint work is included in the lock graph.

PostgreSQL detects deadlocks and aborts one transaction rather than waiting indefinitely. That recovery behavior does not make acquisition order irrelevant. Systems that repeatedly touch parent and child records benefit from treating referential checks as part of the transaction’s lock footprint when defining a consistent access order.

A foreign key is therefore both a declarative integrity rule and a concurrency boundary. Its value comparison is only one part of enforcement. The database also coordinates row lifetimes and key stability across transactions so that a reference validated during a write cannot become invalid through an incompatible concurrent commit.