Supabase Foreign Key Constraint Error— Violation Fixed [2026]
Also covers: insert order · profiles trigger · ON DELETE CASCADE · orphaned rows · delete parent with children
⚡ The Error
{
code: "23503",
message: "insert or update on table "posts" violates
foreign key constraint "posts_user_id_fkey"",
details: "Key (user_id)=(some-uuid) is not present in table "users"."
}✅ Root cause — inserting child row before parent exists
// ✅ Step 1: ensure user/profile exists
// ✅ Step 2: then insert the post with that user_id
const { data: post } = await supabase
.from("posts")
.insert({ title: "Hello", user_id: user.id }) // user.id must exist in usersWhat Causes Foreign Key Constraint Violations
A foreign key is a column that references the primary key of another table. PostgreSQL enforces that the referenced value must exist — you cannot create a posts row with a user_id that doesn't exist in users.
In Supabase, the most common scenarios are: inserting a post before the user's profile row is created, deleting a user without cleaning up their related data, or seeding data in the wrong order.
Insert Parent Row Before Child Row
Root cause — wrong insert orderAlways ensure the parent row exists before inserting child rows. In async code, await each operation before proceeding to the next.
// ❌ Wrong — inserting post before confirming user exists
await supabase.from("posts").insert({ title, user_id: someId })
// ✅ Correct — verify user exists first
const { data: { user } } = await supabase.auth.getUser()
if (!user) throw new Error("Must be logged in")
// Now safe to insert — user.id is guaranteed to exist in auth.users
const { error } = await supabase
.from("posts")
.insert({ title, user_id: user.id })
if (error) console.error(error.message)Fix User Profile Not Created on Signup
profiles table missing row — trigger setupA very common pattern is to have a profiles table that stores extra user data, with a foreign key to auth.users . Set up a PostgreSQL trigger to auto-create the profile row when a new user signs up.
-- Step 1: Create the profiles table
CREATE TABLE profiles (
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
username TEXT UNIQUE,
full_name TEXT,
avatar_url TEXT,
created_at TIMESTAMPTZ DEFAULT NOW()
);
-- Step 2: Enable RLS on profiles
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
-- Step 3: Create the trigger function
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
BEGIN
INSERT INTO public.profiles (id, full_name, avatar_url)
VALUES (
NEW.id,
NEW.raw_user_meta_data ->> 'full_name',
NEW.raw_user_meta_data ->> 'avatar_url'
);
RETURN NEW;
END;
$$;
-- Step 4: Attach trigger to auth.users INSERT
CREATE OR REPLACE TRIGGER on_auth_user_created
AFTER INSERT ON auth.users
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();After adding this trigger, every new Supabase signup automatically creates a corresponding profiles row. Foreign key violations on the profiles table are eliminated.
Set Correct ON DELETE Behaviour
Choose CASCADE, SET NULL, or RESTRICTWhen defining foreign keys, choose the right ON DELETE behaviour for your data model.
-- CASCADE: delete child rows automatically when parent is deleted
-- Use for: posts, comments, files owned by a user
ALTER TABLE posts
ADD CONSTRAINT posts_user_id_fkey
FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE;
-- SET NULL: set the FK column to NULL when parent is deleted
-- Use for: optional relationships (e.g. post assigned to deleted category)
ALTER TABLE posts
ADD CONSTRAINT posts_category_id_fkey
FOREIGN KEY (category_id) REFERENCES categories(id) ON DELETE SET NULL;
-- RESTRICT (default): prevent deletion if child rows exist
-- Use for: critical references where data must be explicitly cleaned up first
ALTER TABLE orders
ADD CONSTRAINT orders_product_id_fkey
FOREIGN KEY (product_id) REFERENCES products(id) ON DELETE RESTRICT;Fix Delete Failing Due to Child Rows
Cannot delete parent row — RESTRICT blockingIf you get a foreign key error when trying to DELETE a row, child rows are referencing it. Either add CASCADE to the foreign key, or delete child rows first.
// Option A: delete children first, then parent
async function deleteUserAndData(userId: string) {
// Delete in reverse dependency order
await supabase.from("comments").delete().eq("user_id", userId)
await supabase.from("posts").delete().eq("user_id", userId)
await supabase.from("profiles").delete().eq("id", userId)
// Finally delete from auth.users (requires service role)
await supabaseAdmin.auth.admin.deleteUser(userId)
}
// Option B: add ON DELETE CASCADE (better long-term)
-- Run in SQL Editor:
ALTER TABLE posts DROP CONSTRAINT posts_user_id_fkey;
ALTER TABLE posts ADD CONSTRAINT posts_user_id_fkey
FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE;Find and Fix Orphaned Rows
Child rows whose parent no longer existsOrphaned rows exist when foreign key constraints were added after data was already inserted, or when CASCADE was not configured. Use SQL to find and clean them up.
-- Find posts whose user_id doesn't exist in auth.users
SELECT p.*
FROM posts p
LEFT JOIN auth.users u ON p.user_id = u.id
WHERE u.id IS NULL;
-- Delete orphaned posts
DELETE FROM posts
WHERE user_id NOT IN (SELECT id FROM auth.users);
-- Find orphaned comments (posts that don't exist)
SELECT c.*
FROM comments c
LEFT JOIN posts p ON c.post_id = p.id
WHERE p.id IS NULL;
-- Add constraint to prevent future orphans
ALTER TABLE posts
ADD CONSTRAINT posts_user_id_fkey
FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE
NOT VALID; -- skip validation of existing rows
-- Then validate when ready (runs in background):
ALTER TABLE posts VALIDATE CONSTRAINT posts_user_id_fkey;Prevention
- Set up the profiles trigger immediately after creating your Supabase project — before any user signs up
- Always use ON DELETE CASCADE for user-owned data (posts, comments, files, profiles)
- Seed data in dependency order — parent tables before child tables
- Always verify the logged-in user exists with getUser() before inserting related data
- Use NOT VALID when adding constraints to existing tables, then VALIDATE separately
- Regularly run orphan queries in staging environments to catch data integrity issues early
Frequently Asked Questions
What is a foreign key constraint violation in Supabase?+−
Why does Supabase say 'insert or update on table violates foreign key constraint'?+−
Why does my profiles table not get a row when a new user signs up?+−
What is ON DELETE CASCADE in Supabase?+−
How do I delete a user and all their data in Supabase?+−
What are orphaned rows in a database?+−
Need Expert Help?
We Design Robust Supabase Database Schemas
Softplix engineers build Supabase schemas with correct foreign keys, triggers, RLS, and cascade behaviour for production apps. Let us help.
Talk to an Engineer