PRIMARY KEY constraint failed

Category: ConstraintVersions: 3.8.0+

🔴 Production Risk Error

Medium — INSERT fails; use UPSERT pattern (INSERT OR REPLACE) if appropriate.

What this means

SQLITE_CONSTRAINT_PRIMARYKEY (1555) is returned when an INSERT or UPDATE would create a duplicate primary key value.

Why it happens

1Inserting a row with an explicit primary key that already exists.
2Manually specifying an INTEGER PRIMARY KEY that conflicts with an existing rowid.

How to reproduce

INSERT or UPDATE where the PK column value already exists.

trigger — this will ERROR

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t(id INTEGER PRIMARY KEY, val TEXT)')
conn.execute('INSERT INTO t VALUES(1, "a")')
try:
    conn.execute('INSERT INTO t VALUES(1, "b")')
except sqlite3.IntegrityError as e:
    print(e)  # UNIQUE constraint failed: t.id

sqlite3.IntegrityError: UNIQUE constraint failed: t.id

Fix 1

Why this works

Use INSERT OR REPLACE or INSERT OR IGNORE if duplicates are expected.

Fix 2

Why this works

Use INTEGER PRIMARY KEY without specifying the value to let SQLite auto-assign.

Fix 3

Why this works

Use ON CONFLICT REPLACE at table definition time.

Sources

📚 Official docs: https://www.sqlite.org/rescode.html#constraint_primarykey

🔧 Source ref: sqlite3.h — SQLITE_CONSTRAINT_PRIMARYKEY = 1555

📖 Further reading: SQLite UPSERT

Confidence assessment

⚠️ MEDIUM confidence

Stable.

PRIMARY KEY constraint failed

What this means

Why it happens

How to reproduce

Fix 1

Fix 2

Fix 3

Sources

Confidence assessment

See also