Which SQL0803 this is. This article covers IBM i’s own message SQL0803 from the Db2 for i SQL message catalog. Db2 for Linux, UNIX, and Windows has a similarly numbered message with an “N” suffix (SQL0803N), which was not checked for this article and is not described below.
Who this is for: RPG and SQL developers who have hit SQLCODE -803 or message ID SQL0803 on an INSERT or UPDATE and need to find which unique index or constraint was violated, which row caused it, and how to make the statement idempotent so it does not happen again.
Prerequisites: basic Db2 for i SQL (INSERT, UPDATE, MERGE), an understanding of unique indexes and unique/primary key constraints (see Creating Tables, Keys, and Constraints with SQL DDL on IBM i), and enough SQLRPGLE to check an SQLCODE/SQLSTATE after an embedded statement.
Verification level: The message text, cause text, recovery text, SQLCODE, and SQLSTATE below are IBM DOC CHECKED, extracted directly from IBM’s own IBM i 7.5 SQL messages and codes PDF (see the References section near the end of this article). The SQLRPGLE example is STATIC CHECKED — reviewed for syntactic plausibility, not compiled or run.
What SQL0803 means
Confirmed verbatim against IBM’s IBM i 7.5 Database — SQL messages and codes reference:
- Message text: “Duplicate key value specified.”
- Cause text: “A unique index or unique constraint &1 in &2 exists over one or more columns of table &3 in &4. The operation cannot be performed because one or more values would have produced a duplicate key in the unique index or constraint.”
- Recovery text: “Change the statement so that duplicate keys are not produced. For information on what rows contain the duplicate key values, look at the previously listed messages in the job log (DSPJOBLOG command) or press F10 (Display messages in job log) on this display.”
- SQLCODE:
-803(the IBM i 7.5 reference lists only the negative form for this message — no documented+803warning form was found in the same listing). - SQLSTATE:
23505.
Unlike SQL0802 (a family of twelve-plus distinct error types under one message number, covered in SQL0802 on IBM i), SQL0803 is a single, specific condition: an INSERT, UPDATE, or a MERGE‘s insert/update action would create two rows with the same value in a column set that is protected by a unique index or a unique/primary key constraint.
Reading the substitution values
The cause text names four substitution values, in order: &1 (the index or constraint name), &2 (its schema/library), &3 (the table name), &4 (the table’s schema/library). The job log’s second-level text (F1 on the SQL0803 entry) substitutes the real names, which is the fastest way to identify exactly which index or constraint was violated — do not guess from the table’s DDL alone if more than one unique index or constraint exists on it.
Common causes
Plain INSERT racing an existing row
The most direct cause: an INSERT supplies a primary key, unique constraint, or unique-index value that already exists in the table.
-- CUSNO is the primary key on ORDLIB.CUSTMST
INSERT INTO ORDLIB.CUSTMST (CUSNO, CUSNAM, CRDLMT)
VALUES (100045, 'ACME METAL WORKS', 25000.00);
-- Fails with SQL0803 if CUSNO 100045 already exists
This is often not a logic error in the SQL itself — it’s a sign the calling program assumed the row did not exist (for example, a re-run of a batch job, a retry after a timeout whose first attempt actually succeeded, or two concurrent processes both trying to create the same customer record).
Concurrent inserts into the same key (a genuine race)
Two jobs can both check “does this key exist?” with a SELECT, both get “no,” and both then INSERT — one succeeds, the other gets SQL0803. The SELECT and the INSERT are not atomic as two separate statements; checking first does not prevent the race, it only makes it less frequent. This is exactly the problem MERGE (see below) and INSERT-with-exception-handling are designed to solve.
UPDATE moving a key into a value that already exists
An UPDATE can produce the same error if it changes a unique or primary key column to a value another row already holds:
-- Fails with SQL0803 if CUSNO 100050 already exists on a different row
UPDATE ORDLIB.CUSTMST
SET CUSNO = 100050
WHERE CUSNO = 100045;
A unique index you forgot about
A table can carry more than one unique index or constraint — for example, a primary key plus a separate unique constraint on a natural key such as an email address or a legacy part number. An INSERT that satisfies the primary key easily can still fail on the second, less obvious unique index. Always read the index/constraint name (&1) from the job log rather than assuming it is the primary key.
Diagnosing SQL0803
1. Read the job log’s F1 detail for the real names
The compile-time message text shows &1–&4 as placeholders; the job log’s actual SQL0803 entry (viewed with DSPJOBLOG, or F10 then F1 on the entry) substitutes the real index/constraint name and table name. This is IBM’s own documented recovery step, not a heuristic.
2. Confirm whether it is the primary key or a secondary unique constraint
Once you have the index/constraint name from the job log, check it against the table’s catalog information:
-- List the unique/primary key constraints on a table and their columns
SELECT CST.CONSTRAINT_NAME, CST.CONSTRAINT_TYPE, KCU.COLUMN_NAME, KCU.ORDINAL_POSITION
FROM QSYS2.SYSCST CST
JOIN QSYS2.SYSCSTCOL KCU
ON KCU.CONSTRAINT_NAME = CST.CONSTRAINT_NAME
AND KCU.CONSTRAINT_SCHEMA = CST.CONSTRAINT_SCHEMA
WHERE CST.TABLE_NAME = 'CUSTMST'
AND CST.TABLE_SCHEMA = 'ORDLIB'
AND CST.CONSTRAINT_TYPE IN ('PRIMARY KEY', 'UNIQUE')
ORDER BY CST.CONSTRAINT_NAME, KCU.ORDINAL_POSITION;
This query is STATIC CHECKED against the documented QSYS2.SYSCST and QSYS2.SYSCSTCOL catalog view names and column names used elsewhere in IBM’s Db2 for i SQL catalog documentation; it was not run against a live system for this article, so verify the exact column names against your release’s catalog views before relying on it.
3. Find the actual duplicate values
Once you know the key columns, find which values are already duplicated (or would become duplicated) by the pending operation:
-- Rows already sharing the key your batch is about to insert
SELECT CUSNO, COUNT(*) AS n
FROM ORDLIB.CUSTMST
WHERE CUSNO IN (100045, 100050, 100099) -- the keys your batch is loading
GROUP BY CUSNO
HAVING COUNT(*) > 1
OR CUSNO IN (SELECT CUSNO FROM ORDLIB.CUSTMST_STAGING); -- illustrative staging table
For a bulk load where many keys might already exist, a direct GROUP BY ... HAVING COUNT(*) > 1 against the target table (to find rows that are already duplicated, which would indicate pre-existing data corruption rather than a load problem) and a separate INTERSECT or EXISTS check between the staging data and the target table (to find keys the load is about to collide with) answer two different, easily confused questions — “is the table already broken” versus “does my incoming batch collide with existing rows.”
Avoiding SQL0803 with upsert patterns
MERGE — the documented, atomic upsert
MERGE performs the existence check and the insert-or-update as a single atomic statement, which avoids the check-then-insert race described above:
MERGE INTO ORDLIB.CUSTMST AS tgt
USING (VALUES (100045, 'ACME METAL WORKS', 25000.00)) AS src (CUSNO, CUSNAM, CRDLMT)
ON tgt.CUSNO = src.CUSNO
WHEN MATCHED THEN
UPDATE SET CUSNAM = src.CUSNAM, CRDLMT = src.CRDLMT
WHEN NOT MATCHED THEN
INSERT (CUSNO, CUSNAM, CRDLMT)
VALUES (src.CUSNO, src.CUSNAM, src.CRDLMT);
MERGE still protects every other unique index or constraint on the table — it only avoids the race on the specific key named in the ON clause. A table with a second unique constraint can still raise SQL0803 out of the WHEN NOT MATCHED THEN INSERT branch if that second constraint is violated; MERGE is not a blanket exemption from every uniqueness rule on the table.
Catching -803 in SQLRPGLE as a last resort
Even with MERGE, a program may need to handle SQL0803 directly — for example, when the key check genuinely must be a separate INSERT for reasons outside SQL’s control, or as a defensive catch around a batch load.
**FREE
ctl-opt dftactgrp(*no) actgrp(*caller);
dcl-s custNo packed(7:0) inz(100045);
dcl-s custNam varchar(40) inz('ACME METAL WORKS');
dcl-s crdLmt packed(11:2) inz(25000.00);
dcl-s sqlCodeN int(10);
dcl-s msgTxt varchar(512);
exec sql
INSERT INTO ORDLIB.CUSTMST (CUSNO, CUSNAM, CRDLMT)
VALUES (:custNo, :custNam, :crdLmt);
sqlCodeN = sqlcode;
select;
when sqlCodeN = 0;
// Row inserted. Normal path.
when sqlCodeN = -803;
// Duplicate key -- the row (or a colliding unique index entry)
// already exists. Not necessarily an application error: this is
// the expected outcome of a retried batch step, a double-submitted
// request, or a genuine race with another job. Decide here whether
// to treat the existing row as success (idempotent retry) or as a
// real conflict to report.
exec sql GET DIAGNOSTICS CONDITION 1 :msgTxt = MESSAGE_TEXT;
// Log custNo and msgTxt. Read the job log for the exact index/
// constraint name (the &1-&4 substitution values) before assuming
// which key collided, if the table has more than one unique index.
other;
// Some other SQLCODE -- not a duplicate-key problem.
exec sql GET DIAGNOSTICS CONDITION 1 :msgTxt = MESSAGE_TEXT;
endsl;
*inlr = *on;
return;
Points to note about this example:
- -803 is not automatically an application bug. Depending on the workflow, a duplicate key on retry can be the correct, idempotent outcome — treat it as “already done” rather than “failed” when that is true for your process, and as a real conflict (and something to surface to an operator) when it is not.
- GET DIAGNOSTICS MESSAGE_TEXT captures the substituted message text (including the real index/constraint and table names) for logging, the same pattern documented for SQL0802 in SQL0802 on IBM i — IBM’s
GET DIAGNOSTICSreference documentsMESSAGE_TEXTasVARCHAR(32740), so a 512-character buffer can truncate a long message. - No automatic retry without changing the data or the key. Retrying the exact same
INSERTagainst the exact same existing row will fail with SQL0803 again, every time — this is a data-state error, not a transient one, unlike a lock-wait condition.
Common mistakes
- Retrying the identical INSERT, expecting a different result. SQL0803 is deterministic given the current data; a bare retry loop will not help.
- Assuming the violated index is the primary key. Read the
&1substitution value (the index/constraint name) from the job log before assuming which uniqueness rule failed, especially on tables with more than one unique index. - Using MERGE and assuming it covers every uniqueness rule on the table.
MERGE‘sONclause protects against the race on the key it tests; a different unique constraint on the same table can still raise SQL0803 from theINSERTbranch. - “Check then insert” as two separate statements under the assumption that checking first prevents the error under concurrency. It reduces frequency, not possibility — only an atomic
MERGE, a locking strategy, or a caught and handled -803 actually closes the race. - Treating every -803 the same way. A duplicate from a retried idempotent step and a duplicate from genuinely conflicting data need different handling; decide which one you are looking at before writing the recovery logic.
A realistic practical use case
A nightly customer-import batch reads a flat extract from an upstream system and inserts new customers into CUSTMST. The job fails partway through with SQL0803 on CUSNO 100045. Investigation shows two things at once, found by reading the job log’s F1 detail and then querying the staging data: first, the batch had already successfully inserted 100045 on a previous, partially-failed run, so this run’s attempt to insert it again is expected, not corruption; second, a different row in the same run, 100099, collides with an existing row that has a different CUSNAM — a genuine data conflict where the upstream system and CUSTMST disagree about who customer 100099 is. The fix converts the plain INSERT loop to a MERGE keyed on CUSNO (making the first case silently idempotent) and adds an explicit comparison of CUSNAM before updating on match, routing a name mismatch to a review queue instead of silently overwriting it.
FAQ
What does SQL0803 mean?
“Duplicate key value specified.” A unique index, unique constraint, or primary key constraint would be violated by the pending INSERT, UPDATE, or MERGE insert/update action. SQLCODE -803, SQLSTATE 23505.
Is there a warning form, +803, like SQL0802 has +802?
The IBM i 7.5 SQL messages and codes reference consulted for this article lists only the -803 form for this message; no +803 warning form was found in that listing. Whether another release documents one was not independently checked.
How do I find out which index or constraint caused it?
Read the job log’s second-level text for the SQL0803 entry (F10 then F1, or DSPJOBLOG) — it substitutes the actual index/constraint name and table name for the &1–&4 placeholders in the documented message text.
Does MERGE eliminate SQL0803 entirely?
No. It eliminates the specific check-then-insert race on the key named in its ON clause. A table with additional unique constraints can still raise SQL0803 from the INSERT branch of a MERGE if one of those other constraints is violated.
Is retrying the same statement a safe recovery?
No, unless something about the data or the key changes first. SQL0803 reflects the current state of the data, not a transient condition like a lock wait.
References
- IBM i 7.5: Database — SQL messages and codes (PDF, © IBM 1998, 2022) — the SQL0803 message text (“Duplicate key value specified.”), cause text (the full
&1 in &2 ... &3 in &4wording and “produced a duplicate key in the unique index or constraint”), recovery text, SQLCODE-803, and SQLSTATE23505. The SQL0803 entry was confirmed by cross-reading its neighboring SQL0802/SQL0804 entries in the same document, since the PDF’s two-column layout can interleave field labels (see the SQL0802 article’s own references section for the same caveat).
An IBM i community blog post (rpgpgm.com) describing a practitioner’s experience getting a generic SQL9010 from RUNSQL and using GET DIAGNOSTICS CONDITION 1 to retrieve the underlying SQL0803 message ID and text was used only as context for the GET DIAGNOSTICS recovery pattern shown above — the message text itself comes from the IBM PDF, not the blog post.
Not independently confirmed: Db2 LUW SQL0803N wording and reason codes; the exact QSYS2.SYSCST/QSYS2.SYSCSTCOL column set on every current release; whether any IBM i release documents a +803 warning form.