SupabasePostgreSQLError Fix2026 Updated

Supabase Foreign Key Constraint Error— Violation Fixed [2026]

Also covers: insert order · profiles trigger · ON DELETE CASCADE · orphaned rows · delete parent with children

January 20266 min readSupabase · PostgreSQL · SQL

⚡ The Error

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

Fix — always insert parent first, then child
// ✅ 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 users

What 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.

1

Insert Parent Row Before Child Row

Root cause — wrong insert order
2 min

Always ensure the parent row exists before inserting child rows. In async code, await each operation before proceeding to the next.

Correct insert order
// ❌ 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)
2

Fix User Profile Not Created on Signup

profiles table missing row — trigger setup
5 min

A 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.

Supabase SQL Editor — create profiles trigger
-- 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.

3

Set Correct ON DELETE Behaviour

Choose CASCADE, SET NULL, or RESTRICT
3 min

When defining foreign keys, choose the right ON DELETE behaviour for your data model.

ON DELETE options — when to use each
-- 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;
4

Fix Delete Failing Due to Child Rows

Cannot delete parent row — RESTRICT blocking
3 min

If 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.

Delete children before parent (without CASCADE)
// 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;
5

Find and Fix Orphaned Rows

Child rows whose parent no longer exists
3 min

Orphaned 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 orphaned rows
-- 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

Frequently Asked Questions

What is a foreign key constraint violation in Supabase?+
A foreign key constraint ensures that a value in one table's column must exist as a primary key in another table. A violation means you are trying to insert a row with a foreign key value that doesn't exist in the parent table — for example, inserting a post with user_id that doesn't exist in the users table.
Why does Supabase say 'insert or update on table violates foreign key constraint'?+
This means you are inserting a row in the child table (e.g. posts) with a foreign key value (e.g. user_id) that does not exist in the parent table (e.g. users). Always insert or verify the parent row exists before inserting the child row.
Why does my profiles table not get a row when a new user signs up?+
Supabase does not automatically create a profiles row when a user signs up. You must set up a PostgreSQL trigger function that fires on INSERT to auth.users and creates the corresponding profiles row. This is a common setup step that many tutorials skip.
What is ON DELETE CASCADE in Supabase?+
ON DELETE CASCADE means that when a parent row is deleted, all child rows referencing it are automatically deleted too. Without CASCADE, deleting a parent row that has child rows fails with a foreign key constraint error. Add CASCADE to foreign keys where child rows should be cleaned up automatically.
How do I delete a user and all their data in Supabase?+
Ensure all tables with user_id foreign keys have ON DELETE CASCADE set. Then delete the user from auth.users using supabaseAdmin.auth.admin.deleteUser(userId). Cascade will handle deleting all related rows automatically.
What are orphaned rows in a database?+
Orphaned rows are child rows whose parent row no longer exists — typically created when the foreign key constraint was added after data was inserted, or when the parent was deleted without proper CASCADE. They cause inconsistencies and can break queries that JOIN parent and child tables.

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