Supabase: can't delete user (foreign key violation)

You click Delete user in the Supabase dashboard, or call supabase.auth.admin.deleteUser(), and get "Database error deleting user". The Postgres log shows a foreign key violation. The cause is almost always one line in a migration, and the fix is a short ALTER TABLE.

The cause

Some table in your schema (often profiles or users) has a column that references auth.users without saying what should happen when the user is deleted. The default is NO ACTION: Postgres refuses to delete a row that other rows still point to. So the user can be deleted only if they never created any data.

Bad: no ON DELETE rule

create table profiles (
  id uuid references auth.users not null primary key,
  full_name text
);

create table posts (
  id bigint generated always as identity primary key,
  user_id uuid references auth.users(id),
  body text
);

This pattern is common in starter templates, which is how it ends up in so many apps. Vercel's official nextjs-subscription-payments starter ships it in its initial migration (supabase/migrations/20230530034630_init.sql, line 7). More examples are on our real bugs page.

Pick CASCADE or SET NULL

ON DELETE CASCADE deletes the user's rows along with the account. Use it for data that only makes sense with the user: profiles, settings, private notes, sessions.
ON DELETE SET NULL keeps the rows and clears the owner. Use it for data other people depend on, such as comments in a shared thread or orders you must keep for accounting. The column must be nullable (no NOT NULL), and your UI has to handle a missing author.

Good: new tables

create table profiles (
  id uuid not null primary key references auth.users(id) on delete cascade,
  full_name text
);

create table posts (
  id bigint generated always as identity primary key,
  user_id uuid references auth.users(id) on delete set null,
  body text
);

The fix for an existing database

You cannot change an existing foreign key in place. Drop it and add it again with the rule, in a new migration. First find the constraint names:

Find every foreign key that points at auth.users

select conrelid::regclass as table_name,
       conname          as constraint_name,
       pg_get_constraintdef(oid) as definition
from pg_constraint
where contype = 'f'
  and confrelid = 'auth.users'::regclass;

Any row whose definition does not end in ON DELETE CASCADE or ON DELETE SET NULL needs fixing. Postgres names them <table>_<column>_fkey by default:

supabase/migrations/20260101000000_fix_user_fks.sql

alter table public.profiles
  drop constraint profiles_id_fkey,
  add constraint profiles_id_fkey
    foreign key (id) references auth.users(id) on delete cascade;

alter table public.posts
  drop constraint posts_user_id_fkey,
  add constraint posts_user_id_fkey
    foreign key (user_id) references auth.users(id) on delete set null;

Both steps run inside one statement, so there is no moment without the constraint. Create the file with supabase migration new fix_user_fks and apply it with supabase db push. Check tables that reference profiles too: if profiles rows now cascade away, their children need a rule as well.

Verify it

Test it: Create a test user, give them a row in every user-owned table, then delete them from Authentication, Users. The delete should succeed, CASCADE tables should have no rows left for that id, and SET NULL tables should keep their rows with an empty owner. Re-run the constraint query above: nothing should be missing a rule.

How Zelsis catches this

Zelsis reads the SQL migrations in your repository and flags every references auth.users line that has no on delete rule, with the file, the line and the fix.

BUG-17Foreign key to auth.users has no ON DELETE rule (user deletion fails)

Check your own repo

Public repositories scan free. Paste a GitHub URL on the dashboard and every finding comes with the file, the line and a fix.

Scan a repository free