datatype mismatch
SQLITE_MISMATCH (result code 20) is returned in the rare cases where SQLite enforces a strict type requirement that its normally-flexible type affinity system cannot satisfy. The most common case is inserting a non-integer into a WITHOUT ROWID table's INTEGER PRIMARY KEY, or violating STRICT mode type rules (SQLite 3.37.0+).
- 1Inserting a non-integer value into the INTEGER PRIMARY KEY column of a WITHOUT ROWID table
- 2Inserting a value that cannot be converted to the declared column type in a STRICT mode table
A text value is inserted into an INTEGER PRIMARY KEY in a WITHOUT ROWID table.
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t (id INTEGER PRIMARY KEY, val TEXT) WITHOUT ROWID')
conn.execute("INSERT INTO t VALUES ('not-an-integer', 'hello')") # triggers SQLITE_MISMATCHexpected output
sqlite3.IntegrityError: datatype mismatch
Fix
Provide an integer value for the primary key
WHEN When using WITHOUT ROWID tables.
conn.execute("INSERT INTO t VALUES (1, 'hello')") # integer pkWhy this works
WITHOUT ROWID tables do not have an implicit rowid so the INTEGER PRIMARY KEY cannot be used as an alias. It must receive a genuine integer value.
✕ Switch to a regular table just to avoid the strict type requirement
WITHOUT ROWID tables are used for specific performance reasons (no extra rowid B-tree). Address the data type issue rather than removing the table optimisation.
STRICT mode tables enforce column types strictly and will produce SQLITE_MISMATCH for any type violation. Without STRICT mode, SQLite uses type affinity and rarely raises this error.
sqlite3.h — SQLITE_MISMATCH = 20
SQLite STRICT tables ↗SQLite WITHOUT ROWID tables ↗Content generated with AI assistance and reviewed for accuracy. Found an error? hello@errcodes.dev