Fix “NOT NULL Constraint Failed” in SQL

The symptom

An INSERT or UPDATE is rejected because a column declaredNOT NULL would end up empty. The whole statement fails; it does not insert the other columns and leave that one blank.

The garden_shop practice tables load straight from CSV and carry no constraints of their own, so the bench below declares a small table with aNOT NULL rule to fail against.

DuckDB names the exact column: Constraint Error: NOT NULL constraint failed: order_imports.customer_email. That is the most useful part of the message. You do not have to guess which column was empty, and on a wide insert that saves real time.

Three ways a value goes missing

The obvious one is an explicit null. The second is easier to miss: leaving a column out of the column list means it takes its default, and a column with no default defaults to null. Neither statement below contains a value you would call wrong.

create or replace table order_imports (
  order_id integer,
  customer_email varchar not null
);

insert into order_imports values (1, null);

-- customer_email is never named, so it defaults to null
insert into order_imports (order_id)
values (2);

The third is a source column that is null in more rows than you expected.orders.coupon_code is populated on 6 of Garden Shop's 24 orders, so selecting it into a NOT NULL column fails on the first null it reaches. A LEFT JOIN that finds no match does the same thing for the same reason, though Garden Shop happens to have no orphaned rows to demonstrate it with.

insert into order_imports
select order_id, coupon_code
from orders;

Count the offending rows first

Before changing anything, count what would fail. "18 rows of 24" is a data problem to think about. "Every row" usually means the wrong column or the wrong join.

select count(*) as total_rows,
       count(*) - count(coupon_code) as rows_without_coupon
from orders

Pick the right fix

There are three, and they are not interchangeable. Supply the value when the data exists somewhere. Give the column a DEFAULT when a sensible placeholder exists and the rule should stay. Drop the NOT NULL rule only when the column is genuinely optional, which is a decision about the model rather than about this one insert.

insert into order_imports
select order_id,
       coalesce(coupon_code, 'NONE')
from orders;

select count(*) as inserted
from order_imports;

create or replace table order_imports (
  order_id integer,
  customer_email varchar not null default 'unknown@example.com'
);

insert into order_imports (order_id)
values (1);

select *
from order_imports;

A DEFAULT and a NOT NULL rule work together rather than against each other: the rule still rejects an explicit null, and the default only covers the case where the column was not mentioned at all.

How other engines word it

Learn more

• SQL error messages explained
• Reference · Table constraints
• Error · Reading a constraint error
• Lesson 7.3 · Safe null handling