violates foreign key constraint

This error occurs when you try to insert a row with a foreign key value that doesn't exist in the referenced table, or delete a row that is referenced by other tables.

The violates foreign key constraint error occurs when a foreign key relationship is broken.

Understanding the Error

ERROR: insert or update on table "orders" violates foreign key constraint "orders_user_id_fkey"
DETAIL: Key (user_id)=(999) is not present in table "users".

Or when deleting:

ERROR: update or delete on table "users" violates foreign key constraint "orders_user_id_fkey" on table "orders"
DETAIL: Key (id)=(1) is still referenced from table "orders".

Common Causes

1. Referencing Non-Existent Parent

SQL

-- If user_id 999 doesn't exist in users table
INSERT INTO orders (user_id, total) VALUES (999, 50.00);  -- Error!

2. Deleting Referenced Parent

SQL

-- If user has orders
DELETE FROM users WHERE id = 1;  -- Error: orders reference this user

3. Wrong Insert Order

SQL

-- Wrong: trying to insert child before parent
INSERT INTO order_items (order_id, product_id) VALUES (1, 100);
INSERT INTO orders (id, user_id) VALUES (1, 1);  -- Too late!

How to Fix It

Solution 1: Insert Parent First

SQL

-- Correct order: parent then child
INSERT INTO users (id, name) VALUES (1, 'Alice');
INSERT INTO orders (user_id, total) VALUES (1, 50.00);

Solution 2: Use CASCADE on Delete

SQL

-- Define cascade behavior when creating the table
CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
  total DECIMAL(10,2)
);

-- Now deleting user also deletes their orders
DELETE FROM users WHERE id = 1;  -- Works, orders deleted too

Solution 3: Use SET NULL on Delete

SQL

CREATE TABLE orders (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
  total DECIMAL(10,2)
);

-- Orders remain but user_id becomes NULL
DELETE FROM users WHERE id = 1;

Solution 4: Delete Children First

SQL

-- Delete in correct order
DELETE FROM order_items WHERE order_id = 1;
DELETE FROM orders WHERE user_id = 1;
DELETE FROM users WHERE id = 1;

Solution 5: Validate Before Insert

SQL

-- Check parent exists before inserting
INSERT INTO orders (user_id, total)
SELECT 1, 50.00
WHERE EXISTS (SELECT 1 FROM users WHERE id = 1);

Solution 6: Defer Constraint Check

SQL

-- Defer constraint checking until transaction commit
SET CONSTRAINTS orders_user_id_fkey DEFERRED;

-- Now you can insert in any order within transaction
BEGIN;
INSERT INTO orders (id, user_id, total) VALUES (1, 1, 50.00);
INSERT INTO users (id, name) VALUES (1, 'Alice');
COMMIT;

Best Practices

Plan insert order - parent tables first
Use CASCADE for tight parent-child relationships
Use SET NULL when children can exist independently
Validate references in application code
Use transactions for multi-table operations