// SQLite error reference

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
Error message
datatype mismatch

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 KEY column.
  • Updating rowid to a text value.
  • Migrating a schema from another database where the id column was text, but declaring it INTEGER PRIMARY KEY in SQLite.

How to fix it

  1. Insert integers (or NULL to let SQLite assign one) into INTEGER PRIMARY KEY columns.

    INSERT INTO users (id, name) VALUES (NULL, 'Ada');
  2. 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

More primary result codes errors

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.

// fewer errors, faster fixes

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.