SQLCODE -803: Duplicate key

What it means

A unique index, including the one behind a primary key or unique constraint, allows each key value only once. Before Db2 adds or changes a row, it checks every unique index on the table. If the new key value already exists, Db2 rejects the statement with -803 and makes no change.

SQLERRMC carries two tokens. The first is the index space name, a short name Db2 generates, which is often not the same as the index name. The second is the RID of the existing row with the same key.

The index that failed isn't always the primary key. Many tables have other unique indexes, on an account number or a reference code for example, and those are easy to forget. Look the index up before you assume which columns clashed.

Common causes

• The row already exists. The classic case is a batch job rerun after it failed partway, so the records it had already inserted go in a second time.
• Two input records carry the same key, so the second insert hits the first.
• A key built by the program, such as MAX(KEY)+1 or a counter in a control table, clashes when two jobs or transactions run at once.
• An identity column or sequence has fallen behind the data. After rows are loaded or copied with explicit key values, Db2 can hand out a number that's already in use.
• An UPDATE changes a key column to a value another row already has.
• The clash is on a second unique index, not the primary key.

How to find the problem

1. Take the index space name from the message and find the index with SELECT CREATOR, NAME, TBNAME, DBNAME FROM SYSIBM.SYSINDEXES WHERE INDEXSPACE = 'CUSTXI01'. The same index space name can exist in more than one database, so check DBNAME.
2. Get the index's key columns from SYSIBM.SYSKEYS, in COLSEQ order.
3. Display the key values the program used, and select the row that already has them.
4. For a rerun, compare the job's input with what's already in the table, and check the first run's output to see how far it got.
5. For an identity column or sequence, compare the highest key in the table with MAXASSIGNEDVAL in SYSIBM.SYSSEQUENCES.

How to fix it

• If the row should be updated when it exists, use MERGE, or catch -803 and issue an UPDATE instead.
• Make reruns safe. Commit at checkpoints and restart from the last committed record, or remove the partial output before running again.
• Move an identity column or sequence past the highest key in use with ALTER TABLE ... ALTER COLUMN ... RESTART WITH n or ALTER SEQUENCE ... RESTART WITH n.
• Stop building keys with MAX+1. A sequence or identity column hands out values safely to many users at once.
• Remove duplicates from an input file before loading it, for example with SUM FIELDS=NONE in a DFSORT step.

What it looks like

CUSTLD01 - INSERT CUSTOMER FAILED, CUST-NO 0041877
DSNT408I SQLCODE = -803, ERROR:  AN INSERTED OR UPDATED VALUE IS INVALID BECAUSE THE INDEX
         IN INDEX SPACE CUSTXI01 CONSTRAINS COLUMNS OF THE TABLE SO NO TWO ROWS CAN
         CONTAIN DUPLICATE VALUES IN THOSE COLUMNS. RID OF EXISTING ROW IS X'0000002A07'
DSNT418I SQLSTATE   = 23505 SQLSTATE RETURN CODE

Last reviewed 2026-10-12 by the WND Software team. Written in our own words. For the full reference, check IBM’s documentation for your z/OS, CICS or Db2 release.