How to fix 23503: Foreign key violation in Supabase

Check that the ID you're trying to reference actually exists in the parent table. For example, if you're inserting an order with user_id = 42, verify that user 42 exists:

SELECT id FROM users WHERE id = 42;

If the query returns no results, the parent record doesn't exist and you need to create it first. You can run this query directly in Supabase's SQL editor.

When working with related data, always create parent records first, then reference them in child records. For example:

-- Step 1: Create the parent record
INSERT INTO users (id, name) VALUES (42, 'John Doe');

-- Step 2: Then reference it in a child record
INSERT INTO orders (id, user_id, amount) VALUES (1, 42, 99.99);

If you try to insert the order before the user exists, you'll get error 23503. In application code, ensure you create parent records before child records in your business logic.

When inserting data across multiple related tables, wrap the operations in a transaction to ensure atomicity. If any insert fails, the entire transaction rolls back:

BEGIN;
INSERT INTO users (id, name) VALUES (42, 'John Doe');
INSERT INTO orders (id, user_id, amount) VALUES (1, 42, 99.99);
COMMIT;

If the second insert fails for any reason, both changes are rolled back, preventing inconsistent state. In Supabase JavaScript client:

const { data, error } = await supabase
  .rpc('insert_user_and_order', {
    user_name: 'John Doe',
    order_amount: 99.99
  });

If you're updating existing records and suddenly hit this error, check for orphaned references:

-- Find orders referencing non-existent users
SELECT o.* FROM orders o
WHERE NOT EXISTS (SELECT 1 FROM users u WHERE u.id = o.user_id);

Delete or update these orphaned records to restore referential integrity:

DELETE FROM orders 
WHERE user_id NOT IN (SELECT id FROM users);

Check the actual foreign key constraint to understand what table and column it references. In Supabase, you can view this in the Table Editor or run:

SELECT constraint_name, table_name, column_name, 
       foreign_table_name, foreign_column_name
FROM information_schema.referential_constraints
WHERE constraint_name LIKE '%fk%' 
AND table_name = 'orders';

Make sure you're inserting values that match the referenced column's type and values.

If you want deletions to be handled automatically, modify the foreign key constraint. For example, to cascade deletes:

ALTER TABLE orders
DROP CONSTRAINT fk_orders_user_id,
ADD CONSTRAINT fk_orders_user_id
FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE;

Common options:
- ON DELETE CASCADE - Delete child records when parent is deleted
- ON DELETE SET NULL - Set foreign key to NULL when parent is deleted (requires column to allow NULL)
- ON DELETE RESTRICT (default) - Prevent deletion if child records exist