Child tables loaded before parent tables in an ETL/ELT pipeline: a fact or transaction table is inserted while the referenced dimension or header table is still loading or empty. This is the most common cause in multi-step ADF or dbt pipelines with incorrect task dependencies.. Parent row deleted while child rows still reference it: a DELETE or TRUNCATE on a parent table (e.g. a lookup or dimension) was executed without first removing or nullifying the child records. ON DELETE CASCADE was not configured, so orphans remain and any subsequent UPDATE on those rows triggers 23503.. Lookup or reference table truncated before reload completes: a full-refresh pattern truncates the parent table and reloads it from source. If a downstream pipeline starts during that window — even briefly — it will fail with 23503 because the parent rows are momentarily absent.. ID mismatch between source and destination during migration or initial load: source system IDs (e.g. integer auto-increments) do not match destination IDs because sequences were reset, tables were re-keyed, or a surrogate key mapping was skipped. Child rows reference source IDs that no longer exist in the destination parent table.. Soft-delete pattern without FK awareness: parent rows are marked deleted (is_deleted = true) but not physically removed, yet the FK points to a separate 'active records' view or partitioned table. Child inserts that reference the soft-deleted parent ID fail because the physical row is still present but the logical target is not.. Race condition in concurrent pipeline runs: two pipeline runs execute in parallel — one loading parents, one loading children. The child pipeline starts before the parent pipeline commits its transaction, so at the moment of insert the parent rows are not yet visible to other sessions.

Step 1 — Read the full error detail to identify the exact constraint and key value: `DETAIL: Key (parent_id)=(123) is not present in table "orders".` Use this to pinpoint which parent table and which ID is missing before any further investigation.. Step 2 — Verify the missing parent row exists (or does not): `SELECT * FROM orders WHERE id = 123;` If this returns nothing, the parent was never loaded or was deleted. If it returns a row, the insert happened before the parent transaction committed — check for concurrent pipeline runs.. Step 3 — Fix load ordering: in your orchestration tool (ADF, Airflow, dbt) make the child pipeline activity depend on successful completion of the parent pipeline activity. In dbt, add a `ref()` dependency so the model DAG enforces correct sequence.. Step 4 — Filter orphan records before insert to prevent hard failure: `INSERT INTO order_lines (...) SELECT ... FROM staging_order_lines s WHERE EXISTS (SELECT 1 FROM orders o WHERE o.id = s.order_id);` Log the excluded rows to a rejection table for downstream review.. Step 5 — Use deferred constraints or a staging approach for bulk loads where ordering is hard to control: `SET CONSTRAINTS ALL DEFERRED;` then perform inserts, then commit. PostgreSQL will validate FK constraints at commit time rather than per-row, giving you time to insert parents and children in the same transaction.. Step 6 — Add a pre-load validation step in dbt or SQL: `SELECT COUNT(*) FROM staging_order_lines s LEFT JOIN orders o ON s.order_id = o.id WHERE o.id IS NULL;` Fail the pipeline explicitly if this count > 0 so you get a clear signal instead of a mid-load 23503.. Step 7 — If orphaned child rows already exist in production and are blocking operations, identify and handle them: `SELECT s.* FROM order_lines s LEFT JOIN orders o ON s.order_id = o.id WHERE o.id IS NULL;` Then either backfill the missing parent rows, set the FK column to NULL (if nullable), or delete the orphans after business sign-off.

High severitydata format

PostgreSQL Error:
23503

Impact

Every occurrence is a data loss event: the child row was rejected and silently dropped from your pipeline. If undetected, downstream aggregations, reports, or SCD merges will be missing rows without any obvious error — the pipeline shows green but the counts are wrong.

The entire batch or COPY statement fails and is rolled back — no partial writes. In ADF this surfaces as a failed activity, halting dependent activities downstream. In dbt the model run fails and all models that `ref()` it are skipped, leaving downstream tables stale from the last successful run. If retries are configured without fixing the root cause (load ordering or missing parent data), each retry will fail identically, consuming retry budget and delaying SLA recovery. Incremental models may accumulate a growing backlog of unprocessed rows.

What does this error mean?

Error 23503 is raised when an INSERT or UPDATE attempts to write a value in a foreign key column that has no matching row in the referenced parent table. PostgreSQL enforces referential integrity at write time, so the operation is immediately rejected and rolled back. In a data pipeline context this typically surfaces when child records arrive before their parent rows exist — for example, loading a fact table before dimension tables are fully populated, or inserting order lines when the corresponding order header was rejected or not yet written. The engineer sees a hard failure with a DETAIL line identifying exactly which key value was missing.

Common causes

1Child tables loaded before parent tables in an ETL/ELT pipeline: a fact or transaction table is inserted while the referenced dimension or header table is still loading or empty. This is the most common cause in multi-step ADF or dbt pipelines with incorrect task dependencies.
2Parent row deleted while child rows still reference it: a DELETE or TRUNCATE on a parent table (e.g. a lookup or dimension) was executed without first removing or nullifying the child records. ON DELETE CASCADE was not configured, so orphans remain and any subsequent UPDATE on those rows triggers 23503.
3Lookup or reference table truncated before reload completes: a full-refresh pattern truncates the parent table and reloads it from source. If a downstream pipeline starts during that window — even briefly — it will fail with 23503 because the parent rows are momentarily absent.
4ID mismatch between source and destination during migration or initial load: source system IDs (e.g. integer auto-increments) do not match destination IDs because sequences were reset, tables were re-keyed, or a surrogate key mapping was skipped. Child rows reference source IDs that no longer exist in the destination parent table.
5Soft-delete pattern without FK awareness: parent rows are marked deleted (is_deleted = true) but not physically removed, yet the FK points to a separate 'active records' view or partitioned table. Child inserts that reference the soft-deleted parent ID fail because the physical row is still present but the logical target is not.
6Race condition in concurrent pipeline runs: two pipeline runs execute in parallel — one loading parents, one loading children. The child pipeline starts before the parent pipeline commits its transaction, so at the moment of insert the parent rows are not yet visible to other sessions.

How to fix it

1Step 1 — Read the full error detail to identify the exact constraint and key value: `DETAIL: Key (parent_id)=(123) is not present in table "orders".` Use this to pinpoint which parent table and which ID is missing before any further investigation.
2Step 2 — Verify the missing parent row exists (or does not): `SELECT * FROM orders WHERE id = 123;` If this returns nothing, the parent was never loaded or was deleted. If it returns a row, the insert happened before the parent transaction committed — check for concurrent pipeline runs.
3Step 3 — Fix load ordering: in your orchestration tool (ADF, Airflow, dbt) make the child pipeline activity depend on successful completion of the parent pipeline activity. In dbt, add a `ref()` dependency so the model DAG enforces correct sequence.
4Step 4 — Filter orphan records before insert to prevent hard failure: `INSERT INTO order_lines (...) SELECT ... FROM staging_order_lines s WHERE EXISTS (SELECT 1 FROM orders o WHERE o.id = s.order_id);` Log the excluded rows to a rejection table for downstream review.
5Step 5 — Use deferred constraints or a staging approach for bulk loads where ordering is hard to control: `SET CONSTRAINTS ALL DEFERRED;` then perform inserts, then commit. PostgreSQL will validate FK constraints at commit time rather than per-row, giving you time to insert parents and children in the same transaction.
6Step 6 — Add a pre-load validation step in dbt or SQL: `SELECT COUNT(*) FROM staging_order_lines s LEFT JOIN orders o ON s.order_id = o.id WHERE o.id IS NULL;` Fail the pipeline explicitly if this count > 0 so you get a clear signal instead of a mid-load 23503.
7Step 7 — If orphaned child rows already exist in production and are blocking operations, identify and handle them: `SELECT s.* FROM order_lines s LEFT JOIN orders o ON s.order_id = o.id WHERE o.id IS NULL;` Then either backfill the missing parent rows, set the FK column to NULL (if nullable), or delete the orphans after business sign-off.

Example log output

ERROR:  insert or update on table "order_lines" violates foreign key constraint "order_lines_order_id_fkey"
DETAIL:  Key (order_id)=(84712) is not present in table "orders".
CONTEXT:  COPY order_lines, line 1403: "84712	9	29.99	2026-05-10"

Frequently asked questions

How do I find all rows causing the 23503 error before running the pipeline?

Run a LEFT JOIN against the parent table on the FK column before your insert: `SELECT s.order_id FROM staging_order_lines s LEFT JOIN orders o ON s.order_id = o.id WHERE o.id IS NULL;` This returns every orphan key in your staging data. Fix or exclude these rows first, then proceed with the load.

Can I use ON CONFLICT DO NOTHING to skip the 23503 error?

No. ON CONFLICT handles duplicate key violations (23505), not foreign key violations (23503). To skip FK failures you need to pre-filter the data with a WHERE EXISTS / LEFT JOIN IS NULL check, or catch the exception in PL/pgSQL with `EXCEPTION WHEN foreign_key_violation THEN` and log the offending row.

23503 error after a parent table was truncated and reloaded — how to prevent this?

Wrap the truncate-and-reload of the parent table in a single transaction together with the child load, or use a rename-swap pattern (load into a staging table, then swap with the live table atomically). This ensures child inserts never see an empty parent table. Alternatively, use `TRUNCATE parent CASCADE` to also clear children, then reload both in order.

Why does the 23503 error happen intermittently only in parallel pipeline runs?

This is a race condition: the parent pipeline has not yet committed when the child pipeline starts reading from the parent table. PostgreSQL's default isolation (READ COMMITTED) means the child session cannot see uncommitted parent rows. Fix: add an explicit dependency in your orchestrator so the child activity only starts after the parent activity reaches 'Succeeded', not just 'Running'.

Source · www.postgresql.org/docs/current/errcodes-appendix.html