Category: Supabase and databases

Supabase/PostgreSQL: Foreign Key Constraint Blocks User Deletion

Read this guide in Polish

Typical symptom: DELETE FROM auth.users WHERE id = '...'; returns an error of the "update or delete on table \"users\" Violates foreign key contrast \"...\" and the operation failed. The reason is not usually a Supabase per se error, but reference restrictions in Postgres — no ON DELETE CASCADE, latent records in related tables, anonymous triggers, or RLS policies preventing visibility/removement of dependence. This document leads step by step: how to locate the contraint, make sure that the restriction is expected, evaluate safe solutions (cascade, soft deletion, data migration) and how to verify correctness repair without loss of data integrity.

How to Recognize Cause

Check the DELETE error message: Postgres returns the constraint name and source table. If the message says e.g. "violates foreign key constraint \"fk orders user id\" on table \"orders\", this means that the order table has a user id column referring to users.id. The constraint name allows you to locate the problem site without searching the entire database.

If the message does not exist (e.g. removal from application level), use the query for system directories: join pg constraint, pg class and pg attribute or information schema.table constraints along with key column use to list all constraints indicating the auth.users scheme. This excludes omitted dependencies, including those created by migration tools.

Step by step solution

1. 1) Repeat the SQL Editor Supabase error: run DELETE and copy the full error message. Save the constraint name and target table. It's the only sure evidence that the restriction blocks the removal.

2 . 2) Locate constraint: start SELECT query with pg constraint/pg class, for example SELECT conname, conrelid::regclass AS table from, confrelid::regclass AS table to FROM pg constraint WHE confrelid = 'auth.users'::regclass OR conrelid = 'auth.users'::regclass; — you will find a dependent table and a name Constraint.

3 . 3) Check related records: for each table found run SELECT COUNT(*) FROM dependent table WHERE user id = '<user uuid>'; to assess the scale of late records and whether these are expected references or e.g. Test artifacts.

4 . 4) Check RLS and permissions: in the Supabase check that the row level security on the dependent tables does not hide records from taking DELETE or SELECT commands. Use pg policy and SHOW policies in SQL panel, remembering that lack of visibility can be confused with lack of reference.

5 . 5 ) Rate the solution: if the relationship should delete the descendants automatically — prepare the migration ALTER TABLE dependent table DROP CONSTRAINT conname; ALTER TABLE dependent table ADD CONSTRAINT conname FOREIGN KEY (user id) REFERENCES auth.users(id) ON DELETE CASCADE; — test on a copy of the database. If child data must be preserved, plan to transfer user id to NULL/anon or create a substitute record.

6 . 6) Safe execution: make changes in transaction on the environment staging. Do a full backup (pg dump) and restore test before changing. In case of manual deletion, first remove records from dependent table in the same transaction or set user id = NULL where acceptable.

7 . 7) Update migrations: After confirmation of action in staging add operations to the migration system (e.g. migration files) and perform deploy along with a short maintenance window to avoid write/delete racing.

How to see if the problem is gone

Test 1: Repeat DELETE on target user; no error and returned state 1 row affected is a proof of success. At the same time run SELECT COUNT(*) FROM dependent table WHERE user id = '<user uuid>', the result should be 0 or according to plan (e.g. NULLy).

Test 2: Test reference integrity: run SELECT conname, conrelid::regclass, confrelid::regclass FROM pg constraint WHERE contype='f' and make sure that the constraint has the expected definition (ON DELETE CASCADE or NO ACTION as decided). In addition, test the basic scenarios for creating a child record and removing your parent.

What to do

Do not remove contraint or perform ALTER TABLE without backup and testing — this can lead to data integrity loss or leave unwanted orphaned records.

Do not solve the problem only by removing the user from the application audit/logs; if the dependent records remain, this will cause inconsistencies in the reports and errors in the application.