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.

Related Articles#

• Debugging Supabase RLS Issues
• Supabase Auth Error Codes in TypeScript
• Supabase Postgres Functions & Triggers Guide
• How to Query Using JOIN in Supabase