Database field guide · 05
Foreign Keys and Concurrency
Integrity, indexes, locks, and online change
Why referential integrity is also a concurrency protocol, and how Oracle, PostgreSQL, and distributed SQL enforce it.
Franck Pachot7 chaptersforeign keys · locking · migrations
Build the mental model before choosing the mechanism.
This minibook was AI-generated from Franck Pachot's archived blog posts. Links to the original articles are included for source context and verification.
01
A foreign key protects a relationship
The constraint says every non-null child key references a parent key. This simple statement must remain true while parents and children are inserted, updated, and deleted concurrently. The engine therefore needs a coordination protocol, not only a validation query.
- Parent deletion conflicts with concurrent child insertion.
- Key updates have the same integrity problem as deletes.
- Immediate and deferred checking change when violations surface.
02
Locks represent intention
When a child references a parent, the engine protects the parent key against disappearance. When a parent is removed, it must establish that no visible or in-flight child can preserve the relationship. Product lock modes differ because their row-version and lock architectures differ.
- Waiting is often proof that integrity is being preserved.
- Inspect the blocked and blocking statements before blaming the constraint.
- Consistent lock order reduces deadlocks across related tables.
03
Why Oracle often needs the child index
Without an index beginning with foreign-key columns, Oracle may need broader child-table locking when a referenced parent key is deleted or changed. An index gives the engine a precise key range to inspect and coordinate instead of protecting an entire heap search space.
- Index foreign keys when parent deletes or key updates occur concurrently.
- The index may also support parent-to-child navigation.
- Low-selectivity foreign keys still need a concurrency assessment.
04
PostgreSQL makes a different trade
PostgreSQL has shared row lock modes that let it protect referenced parent tuples without reproducing every Oracle table-lock pattern. An unindexed foreign key still makes parent deletes validate children by scanning, but the concurrency consequences are not identical.
- Do not migrate Oracle indexing rules without retesting.
- Performance and locking are separate reasons for a child index.
- Observe pg_locks and the actual conflicting transactions.
05
Distributed foreign keys still scale
A distributed SQL database may read and lock keys on remote tablets, so constraint checks have network cost. Removing foreign keys trades visible database work for application races and repair work. Good distribution, batching, and indexes preserve integrity without abandoning scale.
- Model relationships first, then optimize their physical path.
- Account for the index as a distributed write participant.
- Keep application and constraint datatypes identical.
06
Add constraints online
Large existing tables should separate enforcement of new writes from validation of old rows when the product supports it. Create a not-valid or equivalent constraint, validate existing data in a controlled phase, and monitor the locks each phase requests.
- Clean orphan rows before final validation.
- Index supporting columns before the blocking phase when appropriate.
- Treat migration rollback and retry as part of the design.
07
Field manual
Concrete mechanics, diagnostic evidence, and executable patterns to carry into a real system.
01
See the race a foreign key closes
Without coordination, one transaction can verify that parent 42 exists while another deletes it, after which the first inserts an orphan. A foreign key protects the referenced key during child insertion and checks child references during parent deletion. The exact lock is product-specific, but the invariant requires one operation to wait, fail, or observe the other's committed result.
CREATE TABLE child (
child_id bigint PRIMARY KEY,
parent_id bigint NOT NULL
REFERENCES parent(parent_id)
ON DELETE RESTRICT
);
Index the child for parent-side operations
An index beginning with the foreign-key columns lets the engine establish quickly whether children exist and supports application navigation from parent to children. Oracle can take broader locks for parent deletes when that path is absent. PostgreSQL uses different row lock modes, but still scans the child table for each parent-side check without a useful index. Composite foreign keys require the same leading column set and compatible datatypes.
CREATE INDEX child_parent_fk_ix ON child(parent_id);
EXPLAIN (ANALYZE, BUFFERS)
SELECT 1 FROM child WHERE parent_id = 42 LIMIT 1;
Add integrity without one long outage
PostgreSQL NOT VALID skips the historical scan while enforcing the foreign key for new or changed rows. VALIDATE CONSTRAINT later scans old data with a lock compatible with normal reads and writes, though conflicting DDL still waits. Create the supporting child index concurrently outside a transaction, clean orphans, add the constraint, then validate under monitoring.
CREATE INDEX CONCURRENTLY child_parent_fk_ix ON child(parent_id);
ALTER TABLE child ADD CONSTRAINT child_parent_fk
FOREIGN KEY (parent_id) REFERENCES parent(parent_id) NOT VALID;
ALTER TABLE child VALIDATE CONSTRAINT child_parent_fk;
Diagnose the blocker as a transaction graph
A waiting statement is only the visible edge. Capture both sessions, their transaction age, SQL, lock mode, and application identity. In PostgreSQL pg_blocking_pids returns direct blockers; pg_locks explains the protected object. In Oracle join V$SESSION blocking identifiers with V$LOCK or use ASH. The durable fix is usually shorter transactions, consistent object order, or a precise index.
SELECT pid, application_name, xact_start, wait_event, query,
pg_blocking_pids(pid) AS blockers
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;
08
Source articles
Optional deep dives with the complete experiments and product-version context behind this guide.
