SQLITE_MISMATCH: datatype mismatch
One of the few places SQLite is strict about types: most often, setting a rowid or INTEGER PRIMARY KEY column to something that isn't an integer.
- Result code
SQLITE_MISMATCH- Name
20- Category
- Primary result codes
What it means
SQLite normally lets you store any value in any column. SQLITE_MISMATCH (20) is returned in the few cases where it insists on a type. The one the SQLite documentation calls out is the rowid: a table's rowid, and a column declared INTEGER PRIMARY KEY (which is an alias for it), must hold an integer. Setting it to text such as 'abc' or a non-integral real fails with datatype mismatch; NULL is allowed and picks the next free integer.
Type errors in STRICT tables are reported differently, as SQLITE_CONSTRAINT_DATATYPE.
Common causes
- Inserting a non-integer value, such as a UUID string, into an
INTEGER PRIMARY KEYcolumn. - Updating
rowidto a text value. - Migrating a schema from another database where the id column was text, but declaring it
INTEGER PRIMARY KEYin SQLite.
How to fix it
-
Insert integers (or NULL to let SQLite assign one) into
INTEGER PRIMARY KEYcolumns.INSERT INTO users (id, name) VALUES (NULL, 'Ada');
-
If the key really is text, declare it as a text primary key instead.
CREATE TABLE users ( id TEXT PRIMARY KEY, name TEXT NOT NULL );
Reproduce it safely
CREATE TABLE t (id INTEGER PRIMARY KEY);
INSERT INTO t VALUES ('abc'); -- Runtime error: datatype mismatch (20)
Related errors
SQLITE_CONSTRAINT_DATATYPEvalue doesn't fit a STRICT columnSQLITE_CONSTRAINT_PRIMARYKEYPRIMARY KEY constraint failed245SQL Server: Conversion failed converting a value22P02PostgreSQL: invalid input syntax for type
More primary result codes errors
SQLITE_ERRORSQL logic errorSQLITE_PERMaccess permission deniedSQLITE_ABORTquery abortedSQLITE_BUSYdatabase is lockedSQLITE_LOCKEDdatabase table is lockedSQLITE_NOMEMout of memorySQLITE_READONLYattempt to write a readonly databaseSQLITE_INTERRUPTinterrupted
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.