EC
SQLITE_CONSTRAINT_PRIMARYKEY
SQLiteERRORCommonConstraint ViolationHIGH confidence

UNIQUE constraint failed (primary key)

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
  1. 1Inserting a row with a primary key value that already exists in the table
  2. 2Updating a row's primary key to collide with another existing row
  3. 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
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

expected output

sqlite3.IntegrityError: UNIQUE constraint failed: products.id

Fix

Use AUTOINCREMENT or let SQLite assign rowids

WHEN When primary key values should be assigned automatically.

Use AUTOINCREMENT or let SQLite assign rowids
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

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 documentation ↗

sqlite3.h — SQLITE_CONSTRAINT_PRIMARYKEY = 1555

SQLite AUTOINCREMENT documentation ↗

Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev

← All SQLite errors