SQLite: datatype mismatch (tested)

sqlite3.IntegrityError: datatype mismatch is surprising if you know SQLite normally lets any value into any column regardless of its declared type. We measured the one specific place that rule doesn't apply.

1. A normal INTEGER column accepts text fine

SQLite uses "type affinity," not strict typing, by default: a column declared INTEGER is a hint, not an enforced constraint.

con.execute("CREATE TABLE t2 (id INTEGER, name TEXT)")
con.execute("INSERT INTO t2 (id, name) VALUES (?, ?)", ("notanumber", "x"))
# no error — row stored exactly as ("notanumber", "x")

2. The one exception: INTEGER PRIMARY KEY

Same non-numeric text, but the target column is specifically INTEGER PRIMARY KEY (SQLite's rowid alias, not just a regular integer-typed column):

con.execute("CREATE TABLE t (id INTEGER PRIMARY KEY, name TEXT)")
con.execute("INSERT INTO t (id, name) VALUES (?, ?)", ("notanumber", "x"))
sqlite3.IntegrityError: datatype mismatch

Measured: INTEGER PRIMARY KEY is a special case in SQLite — it's a direct alias for the row's internal rowid, which must genuinely be an integer (or NULL, to auto-assign one). Type affinity's usual "store whatever you're given" behavior doesn't apply there, which is why this one column type enforces what every other INTEGER-declared column doesn't.

3. For comparison: a STRICT table gives a different error entirely

SQLite 3.37+'s opt-in STRICT tables enforce types on every column, not just the primary key — and the error text is different:

con.execute("CREATE TABLE t3 (id INTEGER, age INTEGER) STRICT")
con.execute("INSERT INTO t3 (id, age) VALUES (1, \'notanumber\')")
sqlite3.OperationalError: cannot store TEXT value in INTEGER column t3.age

Don't pattern-match on the exception class or exact wording to diagnose which rule you hit: "datatype mismatch" is specifically the INTEGER PRIMARY KEY rowid rule (an IntegrityError); the STRICT-table message is a different, broader rule (an OperationalError).

4. The fix: pass a real integer, or NULL to auto-assign

For an INTEGER PRIMARY KEY column, either supply an actual integer value or NULL (which we confirmed lets SQLite auto-assign the next rowid, the normal auto-increment-like pattern). Convert or validate the value before binding it — this is a real place where SQLite's normally forgiving typing will reject bad input other columns would have silently accepted.

Frequently asked questions

Why does SQLite reject text here but accept it in other INTEGER columns?

We measured the specific reason: INTEGER PRIMARY KEY is a direct alias for the row's internal rowid, which must genuinely be an integer or NULL. SQLite's usual type-affinity behavior (store whatever you're given) is a general rule that this one special column type overrides.

Is 'datatype mismatch' the same error as SQLite's STRICT table type enforcement?

No — we measured them as genuinely different: 'datatype mismatch' (IntegrityError) is specifically the INTEGER PRIMARY KEY rowid rule, while a STRICT table's violation gives 'cannot store TEXT value in INTEGER column' (OperationalError) for any column, not just the primary key.

Does passing NULL for an INTEGER PRIMARY KEY cause this error?

No — we confirmed NULL is accepted and triggers SQLite's normal rowid auto-assignment, the same mechanism behind auto-incrementing IDs. The error is specifically about non-NULL, non-integer values like text.

Why would an app accidentally pass text into an integer primary key column?

Commonly, an upstream value that's always a string in your code (form input, a UUID-like string, a value read from an API) gets bound directly without converting to int first — we reproduced this exact pattern with a plain string literal.

Does this affect a composite or non-rowid primary key the same way?

Not tested here — we specifically measured the single-column INTEGER PRIMARY KEY rowid-alias case. A composite primary key or a WITHOUT ROWID table follows different rules per SQLite's own docs, not covered by this measurement.

Written with AI assistance. Measured on 2026-10-03 with Python 3.13.5's built-in sqlite3 module and SQLite 3.46.1, comparing a plain INTEGER column against an INTEGER PRIMARY KEY column, and separately against a STRICT table, all given the same non-numeric text value. Every error string below is copy-pasted output, not paraphrased. PlainHeaders is our extension; Python's sqlite3 is built in.