SQLITE_MISMATCHERRORTier 1 — Safe✅ HIGH confidence

datatype mismatch

Category: Type ErrorVersions: All SQLite versions

What this means

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+).

Why it happens

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

How to reproduce

A text value is inserted into an INTEGER PRIMARY KEY in a WITHOUT ROWID table.

trigger — this will ERROR

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_MISMATCH

sqlite3.IntegrityError: datatype mismatch

Fix 1: Provide an integer value for the primary key

When using WITHOUT ROWID tables.

fix

conn.execute("INSERT INTO t VALUES (1, 'hello')")  # integer pk

Why 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.

What not to do

✗

Switch to a regular table just to avoid the strict type requirement

Why it's wrong: 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.

Version notes

SQLite 3.37.0+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.

Sources

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

🔧 Source ref: sqlite3.h — SQLITE_MISMATCH = 20

📖 Further reading: SQLite STRICT tables

📖 Further reading: SQLite WITHOUT ROWID tables

Confidence assessment

✅ HIGH confidence

Stable. STRICT mode documentation is clear and confirmed from SQLite 3.37.0 release notes.