UNIQUE constraint failed (primary key)

Category: Constraint ViolationVersions: SQLite 3.7.16+

What this means

SQLITE_CONSTRAINT_PRIMARYKEY (extended code 1555) is the specific constraint violation raised when an INSERT or UPDATE produces a duplicate value for a column that is declared as the PRIMARY KEY. In Python's sqlite3 module it surfaces as sqlite3.IntegrityError with a message identifying the table and column.

Why it happens

1Inserting a row with a primary key value that already exists in the table
2Updating a row's primary key to collide with another existing row
3Using an integer primary key value that was already issued by an autoincrement sequence

How to reproduce

An explicit integer primary key is inserted twice.

trigger — this will ERROR

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT)')
conn.execute("INSERT INTO products VALUES (1, 'Widget')")
conn.execute("INSERT INTO products VALUES (1, 'Gadget')")  # triggers 1555

sqlite3.IntegrityError: UNIQUE constraint failed: products.id

Fix 1: Use AUTOINCREMENT or let SQLite assign rowids

When primary key values should be assigned automatically.

fix

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE products (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT)')
conn.execute("INSERT INTO products (name) VALUES ('Widget')")
conn.execute("INSERT INTO products (name) VALUES ('Gadget')")
print(conn.execute('SELECT * FROM products').fetchall())

Why this works

Without an explicit value for id, SQLite assigns the next available rowid. AUTOINCREMENT additionally guarantees monotonically increasing values — without it, SQLite may reuse a deleted row's id.

What not to do

✗

Add AUTOINCREMENT to every table by default

Why it's wrong: AUTOINCREMENT is slower than plain INTEGER PRIMARY KEY because it requires a separate sqlite_sequence table lookup on every insert. Only use it when monotonicity (no id reuse) is a strict requirement.

Version notes

SQLite 3.7.16+Extended code 1555 introduced. Earlier versions return the base SQLITE_CONSTRAINT code without distinguishing the constraint type.

Sources

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

🔧 Source ref: sqlite3.h — SQLITE_CONSTRAINT_PRIMARYKEY = 1555

📖 Further reading: SQLite AUTOINCREMENT documentation

Confidence assessment

✅ HIGH confidence

Stable. Extended code documented in sqlite3.h. AUTOINCREMENT semantics are well specified.