SQLITE_CONSTRAINT_DATATYPE: value doesn't fit a STRICT column
A STRICT table rejected a value whose type doesn't match the column's declared type, such as text in an INTEGER column.
- Result code
SQLITE_CONSTRAINT_DATATYPE- Name
3091- Category
- Extended result codes
What it means
STRICT tables (SQLite 3.37 and later) enforce column types: an INTEGER column only accepts integers (or text and reals that convert to an integer without loss), TEXT only text, and so on. A value that doesn't fit fails with SQLITE_CONSTRAINT_DATATYPE (3091). Ordinary tables use type affinity instead and store the value anyway.
Common causes
- Inserting a string such as
'12 pcs'into anINTEGERcolumn of a STRICT table. - A driver binding numbers as text, or dates as a type the column doesn't accept.
How to fix it
-
Convert the value before storing it, or bind it with the right type.
INSERT INTO t (qty) VALUES (CAST('12' AS INTEGER)); -
If a column must hold mixed types, declare it
ANYin the STRICT table.
Reproduce it safely
CREATE TABLE t (qty INTEGER) STRICT;
INSERT INTO t VALUES ('abc'); -- cannot store TEXT value in INTEGER column t.qty
Related errors
SQLITE_MISMATCHdatatype mismatchSQLITE_CONSTRAINTconstraint failed22P02PostgreSQL: invalid input syntax for type1366MySQL: Incorrect value for column245SQL Server: Conversion failed converting a value
More extended result codes errors
SQLITE_ERROR_MISSING_COLLSEQno such collation sequenceSQLITE_BUSY_RECOVERYanother process is recovering the WALSQLITE_LOCKED_SHAREDCACHErecord locked in shared cacheSQLITE_CONSTRAINT_CHECKCHECK constraint failedSQLITE_ABORT_ROLLBACKstatement aborted by ROLLBACKSQLITE_BUSY_SNAPSHOTstale read can't become a writeSQLITE_IOERR_SHORT_READread returned fewer bytesSQLITE_CANTOPEN_ISDIRthe path is a directory
The code and message text are from the SQLite result and error codes documentation. Browse the full SQLite error list or search every database error.
Work on SQLite in SQLly
A native SQL client for macOS, Windows and Linux with schema-aware IntelliSense, guardrails for risky statements and clear error messages, for SQLite and the other databases you use.