“Violates Foreign Key Constraint” — Fix This SQL Error

The symptom

A foreign key says each non-null child key must exist in the referenced parent key. The error means that rule would be broken, and it comes in two directions that read almost the same but need opposite fixes. Add NOT NULL separately when every child row must point at a parent.

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.

drop table if exists order_imports;

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, 99);

drop table if exists order_imports;

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, 2);
select * from order_imports;

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

Related errors

• Fix “Failing Row Contains” Constraint Errors in SQL

  An INSERT or UPDATE broke a NOT NULL or CHECK rule on the table. The detail line shows the row.

• Fix “Duplicate Key Violates Primary Key Constraint” in SQL

  The key you are inserting already exists. Find the existing row before you write.

• Fix “CHECK Constraint Failed” in SQL

  A value is storable but breaks a business rule the table enforces. Read the expression.

Learn more

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