ORA-02291 Integrity Constraint — Parent Row Missing

ORA-02291: integrity constraint (SHOP.FK_ORDER_CUSTOMER) violated - parent key not found. It arrives during bulk loads, ETL cutovers, and parallelized migrations: thousands of child rows rejected because their parents aren't there (yet). The application worked row-by-row for years; the loader just changed the order, the parallelism, or the source — and referential integrity did exactly what it was built to do.

Foreign keys enforce a simple law: no child without its parent. That law spans insert order (parents first), deletions (no orphaning parents without CASCADE or child cleanup), and concurrent loads (two streams racing). Break any of the three and error 02291 rejects the write — loudly, per row, until the loader gives up.

This guide moves from the constraint name to the parent table, lists orphans with anti-joins, restores dependency order, uses deferred constraints for cyclic loads, cleans up strays, and gates future loads with pre-flight orphan checks that fail fast every time.

Read the Constraint Name, Not Just the Code

The message names SHOP.FK_ORDER_CUSTOMER — owner plus constraint — and the dictionary does the rest. Join all_constraints (constraint type R, status, deferrability, delete rule) to all_cons_columns (the child columns in position order), then resolve r_constraint_name back through all_cons_columns to the parent table and its columns. One query returns the complete relationship: child table, child columns, parent table, parent columns, and whether the constraint even permits deferral.

Read the status columns while you're there: STATUS (ENABLED/DISABLED), VALIDATED/NOVALIDATE, DEFERRABLE/DEFERRED. A DISABLED or NOVALIDATE constraint that 'lets everything through' explains the opposite symptom (bad data present, no errors) and warns that re-enabling will surface every accumulated orphan at once. Know the constraint's state before you change the data's.

Save the translation in the ticket: 'FK_ORDER_CUSTOMER = orders.customer_id -> customers.customer_id, immediate, NO ACTION.' That single line lets anyone reproduce the orphan query without re-deriving the relationship — and trend analysis on constraint names shows whether failures cluster on one relationship (topology bug) or scatter (source drift).

-- Constraint name -> full relationship in one join
SELECT c.table_name AS child_table, cc.column_name AS child_col,
  c.r_owner AS parent_owner,
  (SELECT table_name FROM all_constraints
    WHERE owner = c.r_owner AND constraint_name = c.r_constraint_name) AS parent_table,
  c.delete_rule, c.deferrable, c.deferred, c.status
FROM all_constraints c
JOIN all_cons_columns cc
  ON cc.owner = c.owner AND cc.constraint_name = c.constraint_name
WHERE c.owner = 'SHOP' AND c.constraint_name = 'FK_ORDER_CUSTOMER'
ORDER BY cc.position;

Find the Orphans With an Anti-Join

The anti-join is the definitive orphan census: staged (or live) child rows with no matching parent. NOT EXISTS reads most clearly and stops at the first match per row; NOT IN risks NULL semantics (a single NULL parent key voids the whole result); LEFT JOIN ... IS NULL works but scans awkwardly. Standardize on NOT EXISTS and the team reads every orphan query at sight.

Always count before listing: the COUNT(*) with the identical predicate sizes the response (dozens = surgical fixes, millions = topology or source bug) without flooding your terminal. Then list with the child key plus the offending FK value — the pair is what the cleanup acts on. Timestamp the orphans too: a tight recent cluster means ordering, a wide historical scatter means drift, and the two get different owners.

Run the census against staging before production loads, and against production after any parent-side delete or archive job without exception. Archive purges are the quiet orphan factories: the purge deletes parents its WHERE clause matched while children outside its scope keep pointing at the grave. Every purge job earns a post-flight orphan count run by the job itself, failing the batch when strays appear.

-- Count first (sizes the response)
SELECT COUNT(*) AS orphans
FROM stage_orders s
WHERE NOT EXISTS (SELECT 1 FROM shop.customers c
  WHERE c.customer_id = s.customer_id);

-- Then list (child key + offending value + recency)
SELECT s.order_id, s.customer_id, s.loaded_at
FROM stage_orders s
WHERE NOT EXISTS (SELECT 1 FROM shop.customers c
  WHERE c.customer_id = s.customer_id)
ORDER BY s.loaded_at DESC;

Insert Order: Parents Before Children

Dependency order is the primary fix and it belongs in the loader's DAG, not in tribal knowledge. Phase the job: all parent streams to completion, a barrier, then child streams — with the barrier as a first-class DAG node that fails loudly if a parent phase partial-fails. Hash-partitioning rows across workers is fine within a phase; across phases it recreates the race. The incident's 16 workers were correct parallelism applied to the wrong dimension.

For single-database migrations, order the DML the same way: INSERT parents, then children, then create (or enable) the FK — or load with constraints DISABLED and ENABLE ... VALIDATE after, letting validation census all violations at once instead of failing on the first row. Staging tables make this clean: raw files land unconstrained, validated sets move in dependency order, and the live tables never see an orphan.

Document the dependency graph beside the loader config: which tables are parents of which, kept in load order. The graph is the onboarding doc and the incident map in one — when 02291 names a constraint, the graph shows its position and which phase owns it. Topology you can see is topology you can fix quickly and safely every time.

-- Phase 1: parents to completion first
INSERT INTO shop.customers (customer_id, name)
SELECT customer_id, name FROM stage_customers;
COMMIT;
-- (DAG barrier: fail here if parent phase partial-fails)

-- Phase 2: children only after the barrier
INSERT INTO shop.orders (order_id, customer_id, total)
SELECT order_id, customer_id, total FROM stage_orders s
WHERE EXISTS (SELECT 1 FROM shop.customers c
  WHERE c.customer_id = s.customer_id);

-- Strays stay staged (loaded later, never forced)

Deferred Constraints for Cyclic Loads

Some loads can't order cleanly: cyclic references (A points at B, B points at A), single-transaction migrations that must interleave, or windows too tight for phasing. DEFERRABLE INITIALLY DEFERRED constraints move the check to COMMIT — insert in any order inside the transaction, and Oracle validates the whole graph once at commit. Real orphans still fail, with the same 02291, just at COMMIT time instead of row time.

Prefer INITIALLY IMMEDIATE for deferrable constraints you add: day-to-day behavior stays immediate (fail fast per row), and transactions that need freedom call SET CONSTRAINTS ALL DEFERRED explicitly. Blanket INITIALLY DEFERRED hides genuine violations until commit across all sessions, turning quick feedback into end-of-transaction surprises. Deferral is a tool you reach for, not a default you live in.

Convert with care: ALTER TABLE ... MODIFY CONSTRAINT ... DEFERRABLE re-locks briefly, so always convert off-peak. And never defer to mask drift — if the anti-join census shows true orphans (no parent anywhere, at any time), deferral just postpones the failure to COMMIT. Deferral tolerates ordering within a single transaction; only repair fixes the true absence of parents.

-- Make deferral available (default behavior stays immediate)
ALTER TABLE shop.orders
MODIFY CONSTRAINT fk_order_customer DEFERRABLE INITIALLY IMMEDIATE;

-- Single transaction, any order, validated once at COMMIT
SET CONSTRAINTS ALL DEFERRED;
INSERT INTO shop.orders (order_id, customer_id, total)
VALUES (101, 9917, 5000);
INSERT INTO shop.customers (customer_id, name)
VALUES (9917, 'New Buyer');
COMMIT;  -- 02291 here means a REAL orphan, not ordering

-- Check deferrability before relying on it
SELECT constraint_name, deferrable, deferred, status
FROM all_constraints WHERE owner = 'SHOP';

Cleanup: Adopt, Create, or Delete Orphans

True orphans need a fate decision per batch, made with the data owner, not the loader. Adopt: the child points at the wrong parent but the right one exists — UPDATE the FK to the correct key (common after source-system merges that renumbered parents). Create: the parent is genuinely missing but reconstructible — INSERT it from the child's distinct key values plus source defaults, marked for review. Delete: the child is junk (test rows, abandoned carts past retention) — archive then DELETE.

Archive before any destructive fate: INSERT the doomed rows into an orphan_archive table with timestamps and reason codes before UPDATE or DELETE touches them. Finance asking about row counts next quarter gets evidence, not recollection — and a mis-fated batch restores from the archive instead of the backup tapes.

Execute set-based with the same anti-join predicate as the census, so cleanup and diagnosis can't disagree. Re-run the census after each fate batch until zero: the loop (census, decide, act, re-census) is the procedure, and 'zero orphans' is the exit criterion every cleanup ticket records before it may close. Partial cleanups stopping at 'few enough' become next quarter's incident report.

-- Archive first (evidence before destruction)
CREATE TABLE orphan_archive AS
SELECT s.*, SYSDATE AS archived_at, 'stale-cart' AS reason
FROM stage_orders s
WHERE NOT EXISTS (SELECT 1 FROM shop.customers c
  WHERE c.customer_id = s.customer_id);

-- Fate 1: adopt (repoint to the real parent)
-- UPDATE stage_orders SET customer_id = 1001 WHERE customer_id = 9917;

-- Fate 2: create (reconstruct missing parents, flagged)
INSERT INTO shop.customers (customer_id, name)
SELECT DISTINCT s.customer_id, 'REVIEW-' || s.customer_id
FROM stage_orders s
WHERE NOT EXISTS (SELECT 1 FROM shop.customers c
  WHERE c.customer_id = s.customer_id);

-- Fate 3: delete (junk only, archive already taken)
-- DELETE FROM stage_orders s WHERE <junk predicate>;

Prevention: Validate Before Loading

Make orphan-free loads a gate, not a hope. The pre-flight anti-join count must read zero before the loader inserts anything — wire it as the DAG's first node with a hard fail, not a warning email. Warnings get snoozed through quarters; hard gates get fixed before lunch. Extend the gate per relationship: every FK the load touches gets its own census query, generated from the dictionary so new constraints join the gate automatically.

Monitor the error as a topology signal. Alert on the first ORA-02291 after any ETL deploy — ordering regressions never self-heal, and the first occurrence is the cheapest moment to catch them. Trend rejections per constraint across loads: a step-change names the deploy that reordered streams even when the diff looked innocent.

Keep staging tables deferrable and live tables immediate. Staging absorbs out-of-order arrivals with deferred checks at merge time; live tables fail fast per row so application bugs surface instantly. The two-tier discipline gives bulk loads freedom and OLTP strictness from the same constraint definitions — correct by construction. Generate the per-FK census queries from the dictionary so new relationships join the gate with zero manual wiring.

#!/bin/bash
# Pre-flight gate: zero orphans before the loader inserts anything
set -euo pipefail
CONNECT="etl/pass@//db-prod:1521/shop"
ORPHANS=$(sqlplus -S "$CONNECT" << 'EOF'
SET HEADING OFF FEEDBACK OFF
SELECT COUNT(*) FROM stage_orders s
WHERE NOT EXISTS (SELECT 1 FROM shop.customers c
  WHERE c.customer_id = s.customer_id);
EOF
)
ORPHANS=$(echo "$ORPHANS" | tr -d ' ')
if [ "$ORPHANS" != "0" ]; then echo "GATE-FAIL: $ORPHANS orphans"; exit 1; fi
echo "GATE-PASS: zero orphans"