SQL error

Fix “Duplicate Key Violates Primary Key Constraint” in SQL

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

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 oneINSERT 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.

Both rows arrive in one statement

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.

Inspect the incumbent

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.

Every duplicate key in a batch

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.

Keep what is already there

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.

Let the new row win

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

select *
from order_imports;

Watch out

Neither form is a fix for a key that is wrong. If two genuinely different records arrived with the same id, DO NOTHING throws one of them away and DO UPDATE overwrites the other. Both leave you with one record where you should have two, and no error to tell you. Check what the duplicateis before choosing.

How other engines word it

Engine	Message
DuckDB	`Constraint Error: Duplicate key "order_id: 1" violates primary key constraint.`
PostgreSQL	`ERROR: duplicate key value violates unique constraint "order_imports_pkey"`
MySQL	`Duplicate entry '1' for key 'order_imports.PRIMARY'`
SQL Server	`Violation of PRIMARY KEY constraint 'PK_order_imports'. Cannot insert duplicate key in object 'dbo.order_imports'.`
SQLite	`UNIQUE constraint failed: order_imports.order_id`