SQLite: datatype mismatch

Result code

SQLITE_MISMATCH (20)

Product

SQLite library

Versions

SQLite 3.x (examples run on 3.39.2; shell output from 3.50.6)

Category

Datatypes & Limits

Last reviewed

24 October 2026

What it means

SQLite accepts almost any value in any column, but a rowid (and a column declared INTEGER PRIMARY KEY, which is the rowid) must be an integer, and so must LIMIT and OFFSET. A value that cannot be turned into an integer without loss stops the statement with this error.

Ordinary SQLite columns use flexible typing: INSERT INTO t (qty) VALUES ('abc') into an INTEGER column simply stores the text. The SQLite documentation says it is strict about types in only a few cases, and SQLITE_MISMATCH is returned in those. The common one is the rowid: a column declared exactly INTEGER PRIMARY KEY is another name for it, so it can only hold integers. Text or a real number that converts losslessly is accepted ('12' and 14.0 were stored as integers on 3.39.2), while 'INV-001', 15.5, an empty string or a BLOB is rejected. On INSERT, NULL means "assign the next id"; on UPDATE, setting the key to NULL is a datatype mismatch too.

Only that exact declaration makes a rowid alias. INT PRIMARY KEY, BIGINT PRIMARY KEY and the historical quirk INTEGER PRIMARY KEY DESC are ordinary columns with a unique index, so on 3.39.2 they stored 'abc' without an error; PRIMARY KEY (id) as a table constraint on an INTEGER column is an alias and is enforced. LIMIT and OFFSET also need integers: LIMIT '10' works, but LIMIT 1.5, LIMIT 'x' and LIMIT NULL (an unset parameter) fail with this message.

STRICT tables (SQLite 3.37.0 and later) enforce every column's type, but they report a different error: cannot store TEXT value in INTEGER column s.qty, with result code SQLITE_CONSTRAINT (19) and extended code SQLITE_CONSTRAINT_DATATYPE (3091), and PDO shows SQLSTATE 23000. A STRICT table still raises "datatype mismatch" (20) for a bad value in its INTEGER PRIMARY KEY. Both were checked on 3.39.2.

Common causes

• A business reference such as an invoice or order number (INV-001) is written into the INTEGER PRIMARY KEY column.
• Copying or importing rows from text data (CSV, JSON, another database) where some ids are not whole numbers, empty or padded with letters.
• A key generated as text, such as a UUID, is stored in an INTEGER PRIMARY KEY column.
• An UPDATE sets the key column to NULL, a real number or text.
• A LIMIT or OFFSET parameter is NULL, empty, a fraction or non-numeric text, for example a page size taken from a query string without checking it.

Diagnose it

Read-only These statements only read the schema (sqlite_schema and read-only PRAGMA queries) or your own data. They do not change data, settings or the database file, so they are safe to run first.

SELECT name, type, pk
FROM pragma_table_info('invoices');

SELECT sql FROM sqlite_schema WHERE name = 'invoices';

SELECT legacy_id, typeof(legacy_id) AS stored_as
FROM import_invoices
WHERE CAST(legacy_id AS INTEGER) <> legacy_id;

SELECT name, strict
FROM pragma_table_list
WHERE schema = 'main' AND type = 'table';  -- SQLite 3.37.0 and later

How to fix it

Each fix is labelled with what it changes. Try read-only checks and the least invasive fix first, and test changes outside production.

INSERT INTO invoices (ref, amount)
SELECT legacy_id, amount
FROM import_invoices;

BEGIN;
CREATE TABLE invoices_new (id TEXT PRIMARY KEY NOT NULL, amount REAL);
INSERT INTO invoices_new (id, amount) SELECT CAST(id AS TEXT), amount FROM invoices;
DROP TABLE invoices;
ALTER TABLE invoices_new RENAME TO invoices;
COMMIT;

$size = max(1, min(100, (int) ($_GET['size'] ?? 20)));
$stmt = $db->prepare('SELECT * FROM invoices ORDER BY id LIMIT :size');
$stmt->bindValue(':size', $size, PDO::PARAM_INT);
$stmt->execute();

INSERT INTO invoices (id, amount)
SELECT CAST(legacy_id AS INTEGER), amount
FROM import_invoices
WHERE CAST(legacy_id AS INTEGER) = legacy_id;

Examples

An invoice number written into INTEGER PRIMARY KEY

CREATE TABLE invoices (
    id     INTEGER PRIMARY KEY,
    ref    TEXT,
    amount REAL
);

INSERT INTO invoices (id, ref, amount) VALUES ('INV-001', 'March', 120.00);

datatype mismatch

Copying text ids into an integer key

CREATE TABLE invoices (id INTEGER PRIMARY KEY, ref TEXT, amount REAL);
CREATE TABLE import_invoices (legacy_id TEXT, amount REAL);
INSERT INTO import_invoices VALUES ('1001', 10), ('INV-1002', 20), ('1003.5', 5);

INSERT INTO invoices (id, amount)
SELECT legacy_id, amount FROM import_invoices;  -- '1001' is fine, 'INV-1002' is not

datatype mismatch

A LIMIT parameter that was never set

CREATE TABLE invoices (id INTEGER PRIMARY KEY, ref TEXT, amount REAL);
INSERT INTO invoices (ref, amount) VALUES ('A', 10), ('B', 20);

SELECT * FROM invoices ORDER BY id LIMIT NULL;  -- what a missing bound value becomes

datatype mismatch

Setting the key to NULL in an UPDATE

CREATE TABLE invoices (id INTEGER PRIMARY KEY, ref TEXT, amount REAL);
INSERT INTO invoices (ref, amount) VALUES ('A', 10);

UPDATE invoices SET id = NULL WHERE ref = 'A';  -- NULL only means "next id" on INSERT

datatype mismatch

How to prevent it

Use INTEGER PRIMARY KEY only for surrogate ids that SQLite assigns, keep codes and external references in separate TEXT columns, and use a TEXT primary key for UUIDs. Check imported data with a read-only query such as the CAST comparison above before copying it.

Validate numeric parameters (ids, page sizes, offsets) in code and bind them as integers. If you want type errors for every column rather than only the key, use STRICT tables (3.37.0 and later) and handle their "cannot store ... value" constraint error.

Common questions

Learn more on CodeWithSQL

• SQL data types explained
• Primary key vs foreign key

Official documentation

• ROWIDs and the INTEGER PRIMARY KEY
• Datatypes in SQLite (type affinity)
• STRICT tables
• SQLite result and error codes

Last technically reviewed 24 October 2026; examples and diagnostics run on SQLite 3.39.2 (PHP PDO_SQLITE) and the sqlite3 shell 3.50.6. Messages are the text the SQLite library itself returns (sqlite3_errmsg) for the versions shown.