Informix Error -100

Reasons / Common Causes

-100 is the ISAM-level duplicate-key error — the code the storage engine itself returns when a row's value in a column (or column set) covered by a unique index collides with a row already present. It is the same underlying condition as the more familiar -239 ("Could not insert new row — duplicate value in a UNIQUE INDEX") and -268 ("Unique constraint violated"), but reached from a different layer.

Why you sometimes see -100 alone, with no SQL error around it. SQL statements almost always surface -239 or -268 as the primary error, with -100 reported as a secondary annotation (ISAM error: -100) — the SQL layer translates the ISAM engine's refusal into a statement-level error before your application sees it. But the official text names the ISAM calls directly: iswrite, isrewrite, isrewcurr, isaddindex. Code that calls the ISAM library directly — legacy C-ISAM programs, some ESQL/C paths that drop to the ISAM API, and internal engine operations — gets -100 on its own, with no SQL wrapper at all, because there is no SQL statement in the picture to attach a higher-level error to.

Beyond that distinction, the realistic causes:

1. A straightforward duplicate insert or update — the row genuinely already exists. Application logic checked for the value's absence, or assumed it, and was wrong.
2. A check-then-insert race between concurrent sessions. Two sessions check "does this key exist?", both see no, both insert — one wins, the other gets -100. This is by far the most common surprising cause, because it doesn't reproduce reliably and the check that preceded the insert genuinely returned "not found" at the time it ran.
3. A retried operation that already succeeded once. A network blip, a timeout, or an application-layer retry re-sends an insert whose first attempt actually committed — the retry collides with its own earlier success.
4. A SERIAL/SERIAL8/BIGSERIAL column colliding with its own sequence. A row was inserted with an explicit, manually chosen value for the serial column (common during a data migration, a restore, or a manual correction), and the engine's internal "next value" counter later reaches that same number.
5. A non-idempotent load or ETL job run twice against the same source file, or a partially failed load re-run from the start instead of from the failure point.
6. Replication, sync, or dual-write logic inserting a row that already arrived by another path — common in migrations running old and new systems in parallel.
7. A unique index that doesn't match the application's notion of "the same value." The index sees 'ABC' and 'ABC ' (trailing space) — or 'ABC' and 'abc' under a case-sensitive collation — as genuinely different values and permits both; the application treats them as duplicates and is surprised when the database doesn't agree. This produces the opposite complaint ("why did this insert succeed?") but is worth knowing about because it's the mirror image of #7 below, and both stem from the same mismatch.
8. The reverse of #7 — a functional or case-insensitive index (or application-level normalization before the insert) makes two values the application considers different collide at the index.

NULLs are not the cause. Like most SQL engines, Informix's unique indexes treat NULL as distinct from every other NULL — a unique index permits any number of rows with a NULL in the indexed column. If you're chasing a duplicate-key error and the colliding column can be NULL, the actual duplicate is a non-NULL value; don't spend time on the NULL rows.

Solutions / Resolution

1. Find the existing row first — see Diagnostic Checks below. Confirming what's already there, and how it got there, determines everything that follows.
2. If it's a genuine duplicate insert, decide whether the application should overwrite (UPDATE instead of INSERT), skip (check-and-skip, accepting the race in #2 below), or surface the conflict to a human — don't just retry the same INSERT in a loop.
3. If it's a check-then-insert race, stop trying to prevent it with a SELECT before the INSERT — that check cannot be made atomic against a concurrent session at the application layer. Either let the INSERT be the check (catch -100/-239/-268 and treat it as "already exists" rather than a failure), or serialize the critical section (an explicit lock, or a SELECT ... FOR UPDATE against a controlling row) so only one session reaches the insert.
4. If a SERIAL column collided with its own sequence, reset the sequence past the highest existing value:

   -- Find the real high-water mark
   SELECT MAX(id) FROM example;
   
   -- ALTER the serial start point (syntax varies by Informix version;
   -- confirm against the version in use before running)
   ALTER TABLE example MODIFY (id SERIAL(<max+1>));
   

   This is the standard fix after a bulk load, a restore, or any manual insert that specified an explicit serial value — do it as a matter of course after any of those operations, not only after hitting -100.
5. If a load or ETL job isn't idempotent, make it one: MERGE (upsert) instead of blind INSERT, or delete the target range before reloading, or track which source rows already landed and skip them on a re-run.
6. If the mismatch is #7/#8 above (index vs. application notion of "same value"), fix the layer that's wrong: normalize data before insert (TRIM, UPPER) to match a case-sensitive index, or build a functional/case-insensitive index if the application's notion is the correct one.
7. For direct ISAM-level callers (iswrite/isrewrite/isaddindex), check iserrno immediately after the call rather than assuming success — these APIs don't raise SQL exceptions, so a program that doesn't check the return value silently continues past a failed write.

Examples

The straightforward case

CREATE TABLE customer
(
    cust_id   INTEGER,
    email     VARCHAR(100)
);
CREATE UNIQUE INDEX cust_email_uq ON customer (email);

INSERT INTO customer VALUES (1, 'a@example.com');
INSERT INTO customer VALUES (2, 'a@example.com');   -- -100 / -239

The second insert collides on email, not on cust_id — the error reports the row that couldn't go in, not necessarily the column a reader assumes is the culprit. Confirm which index raised it (see Diagnostic Checks) before assuming.

The race, not the data

Two sessions, same near-simultaneous timing:

-- Session A                          -- Session B
SELECT COUNT(*) FROM customer          SELECT COUNT(*) FROM customer
  WHERE email = 'b@example.com';         WHERE email = 'b@example.com';
-- returns 0                          -- returns 0 (both check before either inserts)

INSERT INTO customer                   INSERT INTO customer
  VALUES (3, 'b@example.com');           VALUES (4, 'b@example.com');
-- succeeds                            -- -100 / -239

Both SELECTs correctly reported "not found" — at the time each ran, that was true. Nothing was wrong with the check; the check simply cannot be made atomic against a concurrent insert without additional locking. Retrying session B's identical check-then-insert sequence will eventually hit the same race again under load; the fix is structural (#3 above), not a retry.

The SERIAL collision

CREATE TABLE ticket
(
    ticket_id SERIAL,
    subject   VARCHAR(80)
);

-- Migration script restores historical rows with explicit IDs
INSERT INTO ticket (ticket_id, subject) VALUES (500, 'Legacy ticket');
INSERT INTO ticket (ticket_id, subject) VALUES (900, 'Legacy ticket');

-- Weeks later, normal application inserts using the SERIAL default
INSERT INTO ticket (subject) VALUES ('New ticket');
-- -100 / -239, if the engine's internal counter reaches 900 before this

The manual inserts didn't fail — they succeeded, because a SERIAL column accepts an explicit value like any INTEGER. The problem surfaces later, and against a row that has nothing to do with the migration, when the auto-generated sequence catches up to a value a human already claimed. Reset the sequence (Solutions #4) immediately after any load that specifies explicit serial values, rather than waiting to discover the collision weeks later.

Direct ISAM call, no SQL in sight

/* C-ISAM: iswrite fails on a duplicate key with no SQL statement involved at all */
if (iswrite(fd, &record) < 0) {
    if (iserrno == -100) {
        /* handle duplicate: this key already exists */
    }
}

A program written against the ISAM API directly sees -100 as iserrno, full stop — there is no SQL error to catch instead, and no SQL statement to capture for diagnosis. The evidence has to come from the record contents and the index definition, not from a query log.

Diagnostic Checks

1. Identify which index actually raised it — a table can carry several unique indexes, and the error does not always make it obvious which one collided:

   SELECT i.idxname, i.idxtype, c.colname
   FROM   sysindexes i
   JOIN   systables   t ON i.tabid = t.tabid
   JOIN   syscolumns  c ON c.tabid = t.tabid AND c.colno = i.part1
   WHERE  t.tabname = 'customer'
     AND  i.idxtype = 'U';
   

2. Find the row already holding the value. With the offending column(s) identified from step 1, search directly rather than guessing:

   SELECT * FROM customer WHERE email = 'a@example.com';
   

3. For a SERIAL column, there's no query that reads the engine's internal counter directly — it isn't a separate sequence object. Compare the table's actual maximum against what the application believes the next value should be (its own logged last-insert value, or the value returned by DBINFO('sqlca.sqlerrd1') immediately after a real insert in the same session):

   SELECT MAX(ticket_id) FROM ticket;
   

   A gap between that maximum and what the application expects to insert next is the signature of an explicit value having been inserted out of band (a migration, a restore, a manual fix).
4. Check for concurrent activity if the failure doesn't reproduce on retry — a duplicate that "wasn't there a second ago" is the signature of the race in Reasons #2:

   SELECT sid, username, sql_statement
   FROM   sysmaster:syssqlstat s, sysmaster:sysrstcb r
   WHERE  s.sid = r.sid;
   

   or, more simply, onstat -g ses around the time of the failure to see what else was connected.
5. For a load/ETL job, check whether it was re-run — job logs, a scheduler's history, or the presence of the "duplicate" row with a created timestamp from a previous run of the same job, not from a concurrent one.
6. For ESQL/C or direct ISAM callers, confirm the return code is actually being checked at every write call — a program that ignores iserrno on iswrite/isrewrite will continue past a failed write silently, and the -100 that "appeared later" may have been raised, and discarded, several calls earlier.

Related Errors / Related Topics

• -239 — "Could not insert new row — duplicate value in a UNIQUE INDEX." The SQL-level error most INSERT/UPDATE statements actually surface; -100 is frequently its secondary ISAM-level annotation rather than the error an SQL caller sees on its own.
• -268 — "Unique constraint violated." The SQL-standard-named-constraint form of the same condition — same underlying mechanism as -239, different SQL feature (a CONSTRAINT rather than a bare UNIQUE INDEX) producing the collision.

If you're troubleshooting from an SQL application and see -100 without -239 or -268 alongside it, check whether the code path is calling the ISAM API directly (Reasons, above) before assuming something unusual is happening — that's the ordinary explanation, not an edge case.