“Duplicate Key Violates Primary Key Constraint” — SQL Fix

The symptom

An INSERT or UPDATE is rejected because the key it would write is already in the table. A primary key is a promise that the value appears at most once, and the database is keeping that promise on your behalf.

The garden_shop practice tables load straight from CSV and carry no keys of their own, so the bench below declares a table with a primary key to collide with.

DuckDB has two wordings for this

Which one you get depends on where the duplicate came from, and the difference is a useful clue rather than a quirk.

Colliding with a row that is already in the table names the column and the value: Constraint Error: Duplicate key "order_id: 1" violates primary key constraint.

Two duplicate rows inside one INSERT report differently, without naming the column: Constraint Error: PRIMARY KEY or UNIQUE constraint violation: duplicate key "1". Seeing this second wording tells you the duplicate is in the batch you are loading, not in the table you are loading into, so the fix is upstream, in whatever produced the batch.

create or replace table order_imports (
  order_id integer primary key,
  customer_id integer
);

insert into order_imports values (1, 10), (1, 11);

Find the row that already holds the key

Before deciding anything, look at what is there. The existing row tells you whether this is a genuine duplicate, a re-run of a load that already succeeded, or two different records that were assigned the same id upstream.

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

select *
from order_imports
where order_id = 1;

On a whole batch, ask the same question in bulk: group by the key and keep the groups with more than one row. That turns "one row failed" into "here is every key that will fail", which is the list you actually need.

select customer_id,
       count(*) as rows_with_this_key
from orders
group by customer_id
having count(*) > 1
order by rows_with_this_key desc, customer_id

Pick a fix, and mean it

ON CONFLICT DO NOTHING keeps the row that is already there and silently discards the incoming one. That is the right choice for a load you might run twice, and the wrong choice when the new row is the corrected version.

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

create or replace table order_imports (
  order_id integer primary key,
  customer_id integer
);
insert into order_imports values (1, 10);
insert into order_imports values (1, 11)
  on conflict (order_id) do update set customer_id = excluded.customer_id;
select * from order_imports;

insert into order_imports values (1, 11)
on conflict do nothing;

select *
from order_imports;

ON CONFLICT ... DO UPDATE is an upsert: the incoming values win. Name the conflicting column, and read the new values from excluded, which is the row that was rejected.

insert into order_imports values (1, 11)
on conflict (order_id) do update
  set customer_id = excluded.customer_id;

select *
from order_imports;

The upsert reference covers the other forms: updating only when the incoming row is newer, INSERT OR REPLACE, MERGE, and what each database calls them.

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 “Violates Foreign Key Constraint” in SQL

  A child row points at a parent that is not there, or a parent still has children.

• Fix “NOT NULL Constraint Failed” in SQL

  A required column got no value. Supply one, give the column a default, or let it be null.

Learn more

• SQL error messages explained
• Reference · Table constraints
• Reference · Sequences
• Lesson 18.2 · Check data quality with SQL