Fixing Broken Foreign Key Constraints After Bulk Inserts in Postgres

You ran a bulk insert — maybe a data migration, a seed script, or a COPY from a CSV — and now your database is throwing foreign key violation errors. Queries that worked fine yesterday are blowing up, and you have no clear picture of which rows are the problem or how far the damage goes.

This guide walks you through diagnosing the root cause, finding every orphaned row, and restoring referential integrity cleanly without having to rebuild your schema from scratch.

What you'll learn

• Why foreign key constraints fail during bulk inserts and data loads
• How to identify orphaned rows that violate referential integrity
• Techniques for safely disabling and re-enabling constraints
• How to clean or fix orphaned data before re-enabling constraints
• How to prevent this from happening again with better tooling and practices

Prerequisites

This article assumes you have access to a running PostgreSQL instance (version 12 or later covers everything here), basic familiarity with SQL, and at least read access to the tables involved. Some steps require superuser or table-owner privileges.

Why Bulk Inserts Break Foreign Keys

PostgreSQL enforces foreign key constraints at the row level by default. When you insert a single row via an ORM or a regular INSERT, the database checks the referenced table immediately. If the parent row doesn't exist, the insert fails fast and you see the error right away.

Bulk operations change the picture. Tools like COPY, pg_restore, and bulk INSERT ... SELECT statements can load thousands of rows at once. If the referencing rows arrive before the referenced rows — or if the source data itself has integrity gaps — you end up with orphaned foreign keys sitting quietly in the table.

There are also cases where constraints were explicitly deferred or disabled before the load to maximize throughput, and re-enabling them was either forgotten or failed silently. That's the most common production scenario.

Understanding Deferred vs. Immediate Constraints

PostgreSQL supports two constraint-checking modes. An immediate constraint is checked after every row operation. A deferred constraint is checked at the end of the transaction. You can declare a constraint as DEFERRABLE INITIALLY DEFERRED or DEFERRABLE INITIALLY IMMEDIATE at creation time, or toggle it inside a transaction with SET CONSTRAINTS.

Bulk loads often use deferred constraints to avoid the overhead of per-row lookups against the parent table. The risk is that the end-of-transaction check will fail if the data has integrity problems, and the entire load rolls back. If the constraint was disabled outright (via ALTER TABLE ... DISABLE TRIGGER or session-level hacks), no check runs at all and the bad rows land silently.

Step 1: Identify Which Constraints Are Currently Invalid

Start by checking the system catalog. PostgreSQL records whether each constraint is currently valid or has been marked invalid (which happens when you use ALTER TABLE ... ADD CONSTRAINT ... NOT VALID).

SELECT
  conrelid::regclass AS table_name,
  conname AS constraint_name,
  contype,
  convalidated
FROM pg_constraint
WHERE contype = 'f'
  AND NOT convalidated
ORDER BY table_name;

Any row returned here is a foreign key that PostgreSQL has not fully validated against existing data. These are your immediate targets.

Step 2: Find the Orphaned Rows

Before you can fix anything, you need to know exactly which rows are the problem. Use an anti-join pattern: select every row in the child table where the referenced parent row does not exist.

Assume you have an orders table with a customer_id column referencing customers(id):

SELECT o.*
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

This gives you the full set of orphaned rows. Log the count before you do anything else — it's useful context when you're deciding whether to delete, nullify, or repair the records.

If you have many foreign keys to audit, you can generate these queries programmatically from the catalog:

SELECT
  kcu.table_name AS child_table,
  kcu.column_name AS child_column,
  ccu.table_name AS parent_table,
  ccu.column_name AS parent_column
FROM information_schema.table_constraints tc
JOIN information_schema.key_column_usage kcu
  ON tc.constraint_name = kcu.constraint_name
  AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage ccu
  ON ccu.constraint_name = tc.constraint_name
  AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND tc.table_schema = 'public'
ORDER BY child_table;

Use this output to build individual anti-join queries for each relationship you need to check.

Step 3: Decide What to Do With the Orphaned Rows

You have three practical options, and the right one depends on your data and your application logic.

Option A: Delete the orphaned rows

If the rows genuinely have no valid parent and cannot be recovered, deleting them is the cleanest fix. Do this inside a transaction so you can roll back if the count looks wrong.

BEGIN;

DELETE FROM orders
WHERE customer_id NOT IN (
  SELECT id FROM customers
);

-- Verify the count looks right before committing
SELECT COUNT(*) FROM orders
WHERE customer_id NOT IN (
  SELECT id FROM customers
);

COMMIT;

Option B: Nullify the foreign key column

If the column is nullable and your application can tolerate a null parent reference (for example, a soft-deleted customer), set the offending values to null rather than dropping the row.

UPDATE orders
SET customer_id = NULL
WHERE customer_id NOT IN (
  SELECT id FROM customers
);

Option C: Insert the missing parent rows

If the source data is trustworthy but was loaded in the wrong order, insert placeholder parent rows and then update them with correct data later. This is common in multi-step migrations where related tables arrive in separate batches.

-- Insert stub customers for any orphaned order
INSERT INTO customers (id, created_at)
SELECT DISTINCT o.customer_id, NOW()
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL
ON CONFLICT (id) DO NOTHING;

Step 4: Re-enable or Validate the Constraint

Once the orphaned rows are gone or repaired, you can validate or re-enable the constraint. The approach depends on how it was disabled.

If the constraint was marked NOT VALID

Run VALIDATE CONSTRAINT to scan existing rows and flip the constraint to fully valid. This takes a SHARE UPDATE EXCLUSIVE lock, which is less disruptive than an ACCESS EXCLUSIVE lock, but still blocks schema changes on the table while it runs.

ALTER TABLE orders
  VALIDATE CONSTRAINT orders_customer_id_fkey;

If triggers were disabled

PostgreSQL implements foreign key enforcement via internal triggers. Disabling user triggers with DISABLE TRIGGER ALL also disables FK checks (unless you used the safer DISABLE TRIGGER USER variant). Re-enable with:

ALTER TABLE orders ENABLE TRIGGER ALL;

After re-enabling, confirm no violations exist by running the anti-join query again. If any rows are returned, the constraint will still fail validation — fix them before proceeding.

Common Pitfalls

Forgetting cascading references. Your orphaned rows in orders might themselves be parent rows to another table, like order_items. Deleting from orders without checking downstream can cause a cascade error or leave a second layer of orphans. Always trace the full dependency chain before deleting.

Using NOT IN with NULLs. If customers.id can ever be null (unlikely for a primary key, but worth knowing), the NOT IN (SELECT id FROM customers) pattern will silently return no rows because of SQL's three-valued logic. Use the LEFT JOIN ... WHERE IS NULL pattern instead — it handles nulls correctly.

Locking production tables. VALIDATE CONSTRAINT is safer than dropping and recreating a constraint, but it still holds a lock for the duration of the table scan. On large tables, run this during low-traffic windows or use a maintenance window. Monitor with pg_stat_activity to see lock wait times.

Ignoring replication lag. If your Postgres instance has read replicas, bulk inserts and subsequent schema changes need to propagate before applications reading from replicas will see consistent data. Check replication lag before declaring the fix complete.

Preventing This in the Future

The cleanest approach to bulk loads is to load parent tables before child tables and use NOT VALID constraints strategically. Add the constraint immediately after the parent table is loaded but before the child table is loaded, so referential integrity is enforced from the start without a full retroactive scan.

For ongoing migrations or ETL pipelines, add a post-load validation step that runs the anti-join queries and fails the pipeline if any orphaned rows are found. This catches problems at the source, not days later in production. A simple bash script wrapping psql and checking row counts is enough to gate the pipeline.

If you use an ORM like SQLAlchemy or Django's ORM, prefer its bulk insert methods over raw SQL when possible — they respect the active constraint state and surface violations as Python exceptions rather than silent data corruption.

Wrapping Up

Foreign key violations after bulk inserts are always fixable — the key is diagnosing the shape of the problem before touching any data. Here are your next steps:

• Run the pg_constraint query to find every unvalidated foreign key in your schema.
• Use the anti-join pattern to count orphaned rows for each constraint before deciding how to handle them.
• Choose delete, nullify, or backfill based on whether the missing parent data is recoverable.
• Re-validate constraints using VALIDATE CONSTRAINT rather than dropping and recreating them — it's safer on production tables.
• Add a post-load integrity check to your bulk insert pipeline so violations surface immediately, not in production at 2am.