SQL Error -803 and SQLSTATE 23505 (Duplicate Key): How to Fix It

SQL error code -803 with SQLSTATE 23505 means an INSERT or UPDATE tried to create a duplicate value in a column (or set of columns) that has a primary key or unique index. The database rejected the row to protect the uniqueness rule. To fix it, find the existing row with the same key, then either change the new value, update the existing row instead of inserting, or use a MERGE (upsert) statement that handles both cases.

SQLCODE -803 is IBM Db2’s code for this error. Other databases use the standard SQLSTATE 23505 too, including PostgreSQL, where it appears as “duplicate key value violates unique constraint.” Below is how to find the cause and fix it.

What the error message tells you

A Db2 message looks like this:

SQL0803N One or more values in the INSERT statement, UPDATE statement, or foreign key update caused by a DELETE statement are not valid because the primary key, unique constraint or unique index identified by "2" constrains table "MYSCHEMA.ORDERS" from having duplicate values for the index key. SQLSTATE=23505

• The table: MYSCHEMA.ORDERS in this example.
• The index: “2” is the index ID. Look it up in the catalog to see which columns it covers.

Step 1: Find which index is violated

In Db2 for Linux, UNIX and Windows, query the catalog:

SELECT INDNAME, COLNAMES, UNIQUERULE FROM SYSCAT.INDEXES WHERE TABSCHEMA = 'MYSCHEMA' AND TABNAME = 'ORDERS' AND IID = 2;

The COLNAMES value shows the columns that must be unique, such as +ORDER_ID or +CUSTOMER_ID+ORDER_DATE.

Step 2: Find the existing duplicate

Search for the value you tried to insert:

SELECT * FROM MYSCHEMA.ORDERS WHERE ORDER_ID = 1001;

If you’re loading many rows, check for duplicates inside your own data too:

SELECT ORDER_ID, COUNT(*) FROM STAGING.ORDERS GROUP BY ORDER_ID HAVING COUNT(*) > 1;

Step 3: Choose a fix

Cause	Fix
The row already exists	Update it instead, or use MERGE
Duplicate rows in the data you’re loading	Remove duplicates before inserting, for example with DISTINCT or GROUP BY
Hard-coded key value	Let the database generate it with an identity column or sequence
Identity or sequence out of sync after a manual load	Restart the identity or sequence above the current maximum value
Application retried a successful insert	Make the insert idempotent with MERGE, or check before retrying

Use MERGE to insert or update

MERGE inserts the row if it doesn’t exist and updates it if it does:

MERGE INTO MYSCHEMA.ORDERS T USING (VALUES (1001, 'SHIPPED')) S (ORDER_ID, STATUS) ON T.ORDER_ID = S.ORDER_ID WHEN MATCHED THEN UPDATE SET STATUS = S.STATUS WHEN NOT MATCHED THEN INSERT (ORDER_ID, STATUS) VALUES (S.ORDER_ID, S.STATUS);

In PostgreSQL, the equivalent is INSERT … ON CONFLICT (order_id) DO UPDATE, or DO NOTHING to skip duplicates.

Fix an identity column that’s out of sync

If rows were loaded with explicit IDs, the identity counter may still be lower than the highest ID, so new inserts collide. Find the highest value and restart the counter above it:

SELECT MAX(ORDER_ID) FROM MYSCHEMA.ORDERS;
ALTER TABLE MYSCHEMA.ORDERS ALTER COLUMN ORDER_ID RESTART WITH 5001;

Prevent the error

• Let the database generate primary keys instead of the application.
• Validate and de-duplicate data before bulk loads.
• Catch SQLSTATE 23505 in application code and handle it, such as showing “That username is taken.”
• Avoid removing unique indexes to make the error go away. They protect data integrity.

Related: select distinct values across multiple columns in SQL.

Related: drop a SQL database that’s in use.

Frequently asked questions

Is SQLSTATE 23505 the same in every database?

Yes. It’s the standard code for a unique constraint violation. The vendor-specific code differs, such as -803 in Db2.

Can a NULL value cause a -803 error?

It depends on the index. Some unique indexes allow multiple NULLs and some treat NULL as a value that can appear only once.

Can an UPDATE cause this error?

Yes. Changing a key column to a value another row already has triggers it.