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.

{ name: "PostgreSQL", version: "FK constraints" },
{ name: "Supabase", version: "auth.users / profiles" },
]} />

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;

ON DELETE CASCADE is the right call when children can't exist without the parent — but it removes rows with no further prompt. Use SET NULL if the child should survive but lose the link, or RESTRICT to force callers to clean up first.

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();

wrong="await supabase.from('profiles').insert({ id: user.id }) // runs before/without the auth.users row → 23503"
right="Let an after-insert trigger on auth.users create the profile — the parent always exists first."
/>

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.

Only reference Supabase-managed tables like auth.users by their primary key — primary keys are guaranteed stable, while other columns/constraints "may change at any time."

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.

• Changing an ON DELETE action requires dropping and recreating the constraint — Postgres has no in-place ALTER for the FK action.
• The ON DELETE SET NULL (column_list) column-subset syntax needs PostgreSQL 15+; the bare SET NULL (all columns) works everywhere.

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

Frequently Asked Questions

What does "violates foreign key constraint" mean in Supabase?

It's Postgres 23503. Either a child row's FK points at a parent that doesn't exist ("Key is not present in table parent"), or you're deleting a parent that children still reference ("Key is still referenced"). The DETAIL line tells you which.

How do I fix the profiles / auth.users foreign key error?

Don't insert the profile from the client before the user exists. Use an after-insert trigger on auth.users (a SECURITY DEFINER function) to create the profile, and declare the FK on delete cascade.

How do I delete a parent row that has children?

Choose an ON DELETE action: CASCADE (delete children), SET NULL (clear the FK), SET DEFAULT, or RESTRICT/NO ACTION (block until you remove children). Recreate the constraint with the action you want.

Originally published at https://www.iloveblogs.blog