Fix “Violates Foreign Key Constraint” in SQL

The symptom

A foreign key says every value in the child column must exist in the parent table. The error means that promise would be broken, and it comes in two directions that read almost the same but need opposite fixes.

The garden_shop practice tables load from CSV with no keys, so a foreign key cannot reference customers.customer_id. The bench below declares a small parent table with a key of its own.

Two directions, two fixes

Inserting a child whose parent is missing names the value that could not be found: Constraint Error: Violates foreign key constraint because key "customer_id: 99" does not exist in the referenced table. The fix is upstream: create the parent, or correct the key.

Deleting a parent that still has children says the opposite - the key is referenced: Constraint Error: Violates foreign key constraint because key "customer_id: 1" is still referenced by a foreign key in a different table. The fix is downstream: deal with the children first.

create or replace table customers_ref (
  customer_id integer primary key
);
insert into customers_ref values (1), (2);

create or replace table order_imports (
  order_id integer primary key,
  customer_id integer references customers_ref(customer_id)
);
insert into order_imports values (1, 1);

delete from customers_ref where customer_id = 1;

Find the orphans before you load

A foreign key error tells you about one row. A LEFT JOIN tells you about all of them, which is what you want before a load rather than during one. Keep the rows where the parent side came back null: those are the orphans.

select o.order_id,
       o.customer_id
from orders o
left join customers c on c.customer_id = o.customer_id
where c.customer_id is null
order by o.order_id

On Garden Shop that returns nothing, which is the answer you want: every order points at a customer that exists. A check that returns no rows is a check that passed.

Fixing the child direction

Load the parent table first. That is not a workaround, it is the actual dependency order, and it is why staged loads insert lookups before facts. When the key itself is wrong, fix the key rather than inventing a parent to satisfy it.

insert into customers_ref values (99);

insert into order_imports values (2, 99);

select *
from order_imports
order by order_id;

Fixing the parent direction

Delete the children first, then the parent. Or re-point the children at a parent that should own them, which is usually the better answer when the rows are real and only the key was wrong.

delete from order_imports where customer_id = 1;
delete from customers_ref where customer_id = 1;

select count(*) as customers_left
from customers_ref;

How other engines word it

Learn more

• SQL error messages explained
• Reference · Table constraints
• Lesson 18.4 · Stage, clean, and model
• Lesson 8.5 · Transactions