Features
DB2 SQLCODE -803 (SQLSTATE 23505) means an INSERT or UPDATE tried to create a duplicate value in a column protected by a primary-key, unique constraint, or unique index.
SQLCODE -803 / SQLSTATE 23505 is a DB2 duplicate-key error raised when an INSERT or UPDATE violates a primary-key or unique index. Eliminate or merge the conflicting row, or use MERGE/UPSERT logic to insert only non-duplicate data, to resolve the error.
SQL0803N One or more values in the INSERT, UPDATE, or foreign key UPDATE are not valid because the primary key, unique constraint or unique index "<index-name>" constrains table "<schema>.<table>" from having duplicate values for the key. SQLSTATE=23505, SQLCODE=-803
DB2 raises SQLCODE -803 when an INSERT or UPDATE statement produces a row whose key columns already exist in the target table’s primary key, unique constraint, or unique index. The engine blocks the change to preserve data integrity.The accompanying SQLSTATE 23505 signals a duplicate-key condition, letting applications trap and handle the violation programmatically.
Duplicate key values in the incoming data are the primary cause. They often come from upstream ETL loads, bad source deduplication, or application logic that replays the same row.Concurrent transactions can also collide: one session inserts a key while another tries the same key before the first commits, triggering -803 in the second session.
Locate the conflicting key with the index name in the error message, query the table for that key, and determine whether to delete, update, or merge the row.Use MERGE or INSERT … ON CONFLICT (Db2 11.5+) to insert only when the key is absent, or wrap inserts in TRY/CATCH logic that handles SQLSTATE 23505 gracefully.
ETL batch load fails: Remove duplicates in the staging table or add ROW_NUMBER() filtering before loading.Upsert pattern: Switch from plain INSERT to MERGE so DB2 performs an update when the key exists.
Enforce uniqueness early—apply PRIMARY KEY or UNIQUE constraints on staging tables and validate data before the final load.In Galaxy’s SQL editor, enable “safe-run” mode: preview rows that would violate keys, then auto-generate a MERGE statement to prevent -803 at runtime.
SQLCODE -302 (SQLSTATE 22001) – string truncation; verify column lengths.SQLCODE -407 (SQLSTATE 23502) – NOT NULL violation; supply non-null values or alter column definition.
• Attempting to INSERT duplicate primary-key or unique-index values loaded from flat files or message queues.
• UPDATE statement changing a non-key column that is part of a composite unique index, accidentally duplicating an existing combination.
• Concurrent transactions inserting the same key before one commits, especially in high-concurrency OLTP workloads.
• ETL jobs rerun without clearing staging tables, causing the same rows to load twice.
• SQLCODE -302 (SQLSTATE 22001): Data right-truncation error when a string is too long.• SQLCODE -407 (SQLSTATE 23502): Attempt to insert NULL into a NOT NULL column.• SQLCODE -531 (SQLSTATE 23503): Foreign-key violation due to missing parent row.• SQLCODE -805 (SQLSTATE 51002): DBRM or package not found in the plan.
No, it is non-fatal to the database. Only the offending statement fails; fix or handle duplicates and rerun.
You can trap the code in application logic and proceed, but ignoring without fixing data quality risks later failures.
Only identity or sequence columns auto-generate values. For natural keys you must supply unique data.
Galaxy’s AI copilot converts failing INSERTs into MERGE statements, highlights duplicates before execution, and shares verified upsert code with your team.
.avif){kind=logo}
