Fix Foreign Key Constraint Violation in Supabase (23503)

An insert or a user deletion fails with:

ERROR:  insert or update on table "orders" violates foreign key
constraint "orders_customer_id_fkey"
DETAIL:  Key (customer_id)=(42) is not present in table "customers".

or the other direction:

ERROR:  update or delete on table "customers" violates foreign key
constraint "orders_customer_id_fkey" on table "orders"
DETAIL:  Key (id)=(42) is still referenced from table "orders".

Both are SQLSTATE 23503 (foreign_key_violation). The DETAIL line tells you which direction you're in — and the fix is completely different for each.

Direction 1 — child insert, missing parent#

"Key (customer_id)=(42) is not present in table customers" means you inserted a row whose foreign key points at a parent that doesn't exist. The fix is ordering: insert the parent first.

INSERT INTO customers (id, name) VALUES (42, 'Acme');   -- parent first
INSERT INTO orders (id, customer_id) VALUES (1, 42);    -- now valid

A NULL foreign key is always allowed (it's never checked against the parent), so if the relationship is optional, leaving the FK null is valid. If you're sure the parent exists, suspect a wrong referenced column or a type mismatch — the FK must target a primary key or unique constraint.

Direction 2 — parent delete, still referenced#

"Key (id)=(42) is still referenced from table orders" means children block the delete. The behavior is governed by the FK's ON DELETE action (default is NO ACTION):

Choose deliberately:

-- children are meaningless without the parent (e.g. order line items)
ALTER TABLE order_items
  ADD CONSTRAINT order_items_order_fk
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE;

The classic Supabase case: profiles → auth.users#

By far the most common Supabase 23503: a public.profiles table references auth.users, and the app tries to insert a profile before the user row exists, producing "Key (id)=(...) is not present in table users."

The canonical Supabase pattern populates profiles from a trigger that fires after the user is created — so the parent always exists first:

create table public.profiles (
  id uuid not null references auth.users on delete cascade,
  first_name text,
  last_name text,
  primary key (id)
);
 
create function public.handle_new_user()
returns trigger
language plpgsql
security definer set search_path = ''
as $$
begin
  insert into public.profiles (id, first_name, last_name)
  values (new.id, new.raw_user_meta_data ->> 'first_name',
                  new.raw_user_meta_data ->> 'last_name');
  return new;
end;
$$;
 
create trigger on_auth_user_created
  after insert on auth.users
  for each row execute procedure public.handle_new_user();

The reverse direction also bites: deleting a user via the Auth admin API throws 23503 if a user-defined FK to auth.users uses the default NO ACTION. That's exactly why the profiles definition above uses on delete cascade.

Circular or batch inserts: deferrable constraints#

When two tables reference each other (or a self-reference) and neither row can go first, make the constraint deferrable so the check runs at COMMIT:

ALTER TABLE a ADD CONSTRAINT a_b_fk FOREIGN KEY (b_id) REFERENCES b(id)
  DEFERRABLE INITIALLY DEFERRED;
-- insert both rows in one transaction; the FK is checked at COMMIT

Note RESTRICT cannot be deferred — only NO ACTION checks can.

Dropping and recreating an FK on a live table is itself a migration — before you ship it, the rollback playbook for schema changes lays out the lock-aware sequence and the forward-fix-first mindset that keeps constraint recreation from turning into an outage.

Official references: error code 23503, FK constraints / ON DELETE actions, DEFERRABLE in CREATE TABLE, Supabase managing user data, Supabase cascade deletes.

Postgres 23503#

23503 is the SQLSTATE Postgres assigns to foreign_key_violation. It belongs to class 23 — integrity constraint violation, which also contains 23502 (not_null_violation), 23505 (unique_violation) and 23514 (check_violation). The five-digit code is part of Postgres's published error-code appendix and is stable across versions, locales and server settings — so match on the code, never on the message text, which gets reworded and translated between releases. Through Prisma the same SQLSTATE comes back as error P2003; if it appears when deleting parent rows, see Prisma's P2003 on deleteMany for the delete order and the cascade migration.

Where the code turns up:

• psql — after the ERROR: line, \errverbose prints the full field set: SQLSTATE, CONSTRAINT, SCHEMA, TABLE and DETAIL. That names the offending constraint outright.
• Server logs — emitted at the verbosity level you've configured, prefixed with the SQLSTATE.
• plpgsql — EXCEPTION WHEN foreign_key_violation works because the condition name is a synonym for the code, so handlers never hardcode digits.
• Drivers and PostgREST — the error arrives as a structured object carrying code (the SQLSTATE) plus constraint, table and detail.

If you don't have the error to hand, look the constraint up directly:

select conname,
       conrelid::regclass as child_table,
       confrelid::regclass as parent_table
from pg_constraint
where contype = 'f'
  and conname = 'orders_customer_id_fkey';

contype = 'f' filters to foreign keys; conrelid and confrelid are the OIDs of the child and parent tables, rendered as names by the ::regclass cast. The same information appears in psql's describe output — \d orders lists every FK on the table, what it references, and its ON DELETE action. For a walkthrough of that output, see PostgreSQL DESCRIBE TABLE: The psql \d Equivalent.

The important framing: 23503 means the constraint is present and working. Postgres is not telling you the code is unknown or the constraint is missing — it's telling you a key value has no matching parent row, or a parent row still has dependants. Class 23 errors are raised when the statement's check runs (or at COMMIT, for deferrable constraints), not at parse time, so a multi-row insert fails on the row that breaks it and that statement's work is discarded.