insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"

Why it happens

Postgres raises SQLSTATE 23503 when an INSERT (or UPDATE) writes a foreign-key value that has no matching row in the referenced table at the moment the statement runs. The constraint orders_user_id_fkey says every orders.user_id must point at an existing users.id. When that parent row is absent, the write is rejected:

ERROR:  insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
DETAIL:  Key (user_id)=(a1b2c3d4-...) is not present in table "users".

During a migration there are three realistic causes:

1. Child inserted before parent. The most common cause. The loader wrote orders before users finished, so the referenced user_id did not exist yet. See foreign key for how the dependency graph should drive load order.
2. The parent row silently failed to insert. A duplicate key, a NOT NULL violation, or a type mismatch dropped some users rows. The children that referenced them are now orphans. If the parent table itself was never created you would instead get relation does not exist.
3. A self-referencing or circular FK. A table that references itself (e.g. categories.parent_id -> categories.id), or a cycle between two tables, cannot be satisfied row-by-row in any plain insert order.

How to fix it

First confirm the cause. Find the orphaned child rows — the ones whose parent key is missing on the destination:

-- Run on the DESTINATION database
SELECT o.id, o.user_id
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE o.user_id IS NOT NULL
  AND u.id IS NULL;

If that returns rows, the parents are genuinely absent. Pick the fix that matches your situation.

Fix A — load parents first (preferred). Re-run the parent table, then the child:

-- 1. Load users (parent) completely first
-- 2. Then load orders (child)
-- The FK is satisfied because every referenced users.id already exists.

If the missing parent rows exist in the source but never arrived, re-run the users insert, then re-run the failed orders batch.

Fix B — defer constraints inside one transaction. Use this when parent and child are loaded in the same transaction and a strict order is hard to guarantee. Deferrable constraints are checked at COMMIT, not per row:

BEGIN;
SET CONSTRAINTS ALL DEFERRED;

INSERT INTO orders (id, user_id, total) VALUES (...);   -- child, parent not yet present
INSERT INTO users (id, email)          VALUES (...);   -- parent

COMMIT;  -- all FKs validated here

SET CONSTRAINTS ALL DEFERRED only affects constraints declared DEFERRABLE. If yours are not, mark them first:

ALTER TABLE orders
  ALTER CONSTRAINT orders_user_id_fkey DEFERRABLE INITIALLY IMMEDIATE;

Fix C — drop the FK, bulk-load, re-add it. Best for very large tables where you want no per-row checking during the load:

-- Before loading
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;

-- ... bulk INSERT users, then orders ...

-- After loading: re-adding validates every existing row at once
ALTER TABLE orders
  ADD CONSTRAINT orders_user_id_fkey
  FOREIGN KEY (user_id) REFERENCES users (id);

If this ADD CONSTRAINT fails, the orphan query above tells you exactly which orders.user_id values have no parent. Either backfill the missing users rows or delete the orphaned orders rows before re-adding the constraint:

DELETE FROM orders o
WHERE o.user_id IS NOT NULL
  AND NOT EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id);

How to prevent it

• Topologically sort tables by their FK graph and load parents before children. SupaMigrate builds this order from information_schema.table_constraints and key_column_usage before any data moves, so users always lands before orders.
• Wrap related tables in one transaction with deferred constraints when a clean parent-first order is not possible — for example circular references.
• Migrate auth.users before any table that references it. Public tables keyed on user_id will fail with this exact error if auth is loaded after them.
• After each table loads, run the orphan left-join check rather than waiting for the FK re-add to surface the problem.
• Watch for silently dropped parent rows. A parent insert that hits a unique or NOT NULL violation reduces the parent set; every child that pointed at those rows becomes a 23503 waiting to happen.