Rowid is not unique

Category: ConstraintVersions: 3.8.9+

🔴 Production Risk Error

Medium — same as UNIQUE constraint failure.

What this means

SQLITE_CONSTRAINT_ROWID (2579) is returned when a WITHOUT ROWID table's primary key constraint is violated, analogous to SQLITE_CONSTRAINT_PRIMARYKEY for ordinary tables.

Why it happens

1INSERT of a duplicate primary key into a WITHOUT ROWID table.

How to reproduce

INSERT or UPDATE on a WITHOUT ROWID table with a duplicate primary key.

trigger — this will ERROR

import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t(k TEXT PRIMARY KEY, v TEXT) WITHOUT ROWID')
conn.execute("INSERT INTO t VALUES('key1','val1')")
try:
    conn.execute("INSERT INTO t VALUES('key1','val2')")
except sqlite3.IntegrityError as e:
    print(e)

sqlite3.IntegrityError: UNIQUE constraint failed: t.k

Fix 1

Why this works

Use INSERT OR REPLACE or INSERT OR IGNORE for WITHOUT ROWID tables.

Fix 2

Why this works

Validate primary key uniqueness before inserting.

Version notes

Sources

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

🔧 Source ref: sqlite3.h — SQLITE_CONSTRAINT_ROWID = 2579

📖 Further reading: WITHOUT ROWID tables

Confidence assessment

⚠️ MEDIUM confidence

Stable.