The -803/23505 error appears when an INSERT or UPDATE attempts to create duplicate values in a column or index defined as UNIQUE or PRIMARY KEY.
DB2 SQLCODE -803 / SQLSTATE 23505 occurs when an INSERT or UPDATE would create a duplicate value in a column or index marked UNIQUE or PRIMARY KEY. Remove or change the duplicate, or make the key unique, to resolve the error.
SQLCODE -803, SQLSTATE 23505: One or more values in the INSERT/UPDATE statement violate a UNIQUE constraint or duplicate values in a PRIMARY KEY.
DB2 throws SQLCODE -803 and SQLSTATE 23505 when a row you attempt to INSERT or UPDATE would create a duplicate value in a column or index that enforces uniqueness. The engine rejects the statement to protect data integrity.
The error stops transaction processing and rolls back the current statement.
Quick resolution is crucial because retries without fixes will always fail.
The violation appears during INSERT, MERGE, LOAD, or UPDATE operations against tables with PRIMARY KEY, UNIQUE constraints, or UNIQUE indexes.
Triggers, import utilities, and even replication tools can surface the same error if they inject duplicates.
High-volume batch loads and poorly isolated concurrent transactions are common timing windows for -803 events.
Persisting duplicates breaks referential links, invalidates analytics, and blocks application workflows. Production outages can propagate if retry logic loops without correction.
Fixing the root cause preserves data quality and service availability.
Duplicate primary-key values inserted by application logic, ETL scripts, or manual SQL top the list. Another leading trigger is concurrent inserts that pick identical surrogate keys from a custom key-generation routine.
Accidental reprocessing of the same source file, failure to de-duplicate staging data, or missing WHERE clauses in UPDATE statements also generate duplicates.
Schema changes that add UNIQUE constraints on existing duplicate data will raise -803 immediately.
First, identify the conflicting key using DB2 diagnostic messages or the SQLERRMC token, which shows the constraint name and duplicate value. Query the table for that key to confirm it already exists.
Then decide: modify the incoming data, delete or archive the existing row, or relax the constraint.
Most systems update the offending row or generate a new surrogate key. Ensure only one row per key before re-running the statement.
Batch load duplicates: add SELECT DISTINCT or staging-table deduplication before LOAD. Concurrent inserts: replace homegrown key logic with IDENTITY columns or DB2 sequences. Missing WHERE in UPDATE: add the correct predicate or SET clauses.
Schema evolution failures: cleanse existing duplicates, then add the UNIQUE constraint retry.
Galaxy’s AI copilot can scan for duplicate patterns and suggest corrected SQL before deployment.
Enforce uniqueness at the source system, use DB2 GENERATED ALWAYS identity columns, and encapsulate key generation in stored procedures to avoid race conditions.
Implement UNIQUE constraint checks in CI pipelines.
Galaxy Collections let teams endorse deduplicated queries so production loads stay safe.
SQLCODE -804 indicates dynamic SQL errors but may follow -803 if retries misuse parameter markers. SQLCODE -407 (NULL not allowed) can surface alongside -803 when NULL and duplicate checks combine. Resolve -803 first, then retest to isolate others.
.
No. It covers any constraint or index defined as UNIQUE, including composite unique indexes.
The SQLERRMC string in the DB2 diagnostic message shows the constraint name and offending key data.
Yes, drop or disable the UNIQUE index, handle duplicates, then recreate it. Do so only in controlled maintenance windows.
Galaxy’s AI copilot highlights potential duplicate inserts in queries and surfaces table key metadata inline, preventing bad SQL from reaching production.
.png)
.avif){kind=logo}
