SQLITE_CONSTRAINT_DATATYPEERRORTier 2 — Caution⚠️ MEDIUM confidenceStrict table datatype constraint failed
🔴 Production Risk Error
Low — fails immediately with a clear type mismatch message.
What this means
SQLITE_CONSTRAINT_DATATYPE (3091) is returned when a value inserted into a STRICT table does not match the declared column type. STRICT tables enforce rigid type checking unlike standard SQLite tables.
Why it happens
- 1Inserting a TEXT value into an INTEGER column of a STRICT table.
- 2Inserting a non-numeric value into a REAL or NUMERIC column of a STRICT table.
How to reproduce
INSERT or UPDATE on a STRICT table with a type mismatch.
import sqlite3
conn = sqlite3.connect(':memory:')
conn.execute('CREATE TABLE t(x INTEGER, y TEXT) STRICT')
try:
conn.execute("INSERT INTO t VALUES('not-a-number', 'ok')")
except sqlite3.IntegrityError as e:
print(e) # cannot store TEXT value in INTEGER column t.xFix 1
Why this works
Ensure values match the declared type before inserting into STRICT tables.
Fix 2
Why this works
Cast values explicitly: CAST(? AS INTEGER)
Fix 3
Why this works
If flexible typing is needed, do not use STRICT tables.
Version notes
Sources
📚 Official docs: https://www.sqlite.org/rescode.html#constraint_datatype
🔧 Source ref: sqlite3.h — SQLITE_CONSTRAINT_DATATYPE = 3091
📖 Further reading: SQLite STRICT tables
Confidence assessment
⚠️ MEDIUM confidence
Stable. Added with STRICT table support.
See also
🔗 Related errors
📄 Reference pages