SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

By Sharon Ben-Moshe · September 26, 2026 · 2 min read

Sharon Ben-Moshe is the founder of sqlfmt, a browser-based SQL formatter and validator with misspelled-keyword suggestions across PostgreSQL, MySQL, SQLite, SQL Server, and BigQuery.

Understand SQLite Error 20 (datatype mismatch). Learn how SQLite type affinity, CHECK constraints, and STRICT tables enforce data integrity.

Quick answer

SQLite Error 20 (SQLITE_MISMATCH / datatype mismatch) occurs when a value violates a column's explicit type constraint. While traditional SQLite tables use dynamic type affinity (storing text in integer columns without complaint), STRICT tables (introduced in SQLite 3.37.0) and CHECK constraints strictly reject incompatible types like inserting text into an INT column.

FAQs

Why does SQLite normally allow inserting text into an INTEGER column?

Traditional SQLite uses dynamic typing with 'type affinity'. The declared column type is merely a hint; SQLite attempts to convert the value, but if conversion fails, it happily stores the string as TEXT without throwing an error.

What are SQLite STRICT tables?

Introduced in SQLite 3.37.0 (November 2021), the `STRICT` table option disables type affinity. Any insert that does not match the declared type (INT, REAL, TEXT, BLOB, ANY) fails with SQLITE_MISMATCH (code 20).

How do I create a STRICT table in SQLite?

Append the keyword `STRICT` after the closing parenthesis of the CREATE TABLE statement: `CREATE TABLE users (id INT PRIMARY KEY, name TEXT) STRICT;`.

Can a CHECK constraint cause SQLITE_MISMATCH?

Yes. A constraint like `CHECK (typeof(age) == 'integer')` will reject float or text inputs with a constraint or mismatch error.

SQL Errors

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

By Sharon Ben-Moshe · Sep 29, 2026

Fix Google BigQuery's "Unrecognized name: column" error. Learn alias visibility rules across WHERE and QUALIFY, case sensitivity, and struct unnesting.

Read post

SQL Errors

PostgreSQL statement_timeout (Error 57014): Causes and Prevention

By Sharon Ben-Moshe · Sep 23, 2026

Fix PostgreSQL Error 57014: canceling statement due to statement_timeout. Learn how to tune query plans, configure timeouts per session, and avoid lock waits.

Read post

SQL Errors

Try it now

Paste your SQL into sqlfmt — format, validate, and catch typos free in your browser.

Open SQL Formatter →

Key takeaways

Standard SQLite tables convert types via type affinity; STRICT tables reject type mismatches immediately.

STRICT tables only accept five explicit data types: INT, REAL, TEXT, BLOB, and ANY.

PRIMARY KEY on an INT column in a STRICT table is not the same as INTEGER PRIMARY KEY (rowid alias) unless declared properly.

Audit ORM schemas (like Prisma, Drizzle, or Room) for type conversions when enabling STRICT mode.

Type affinity versus STRICT tables in action

Compare how standard SQLite tables handle invalid types versus modern STRICT tables:

Comparing standard and strict table behavior in SQLite

-- Traditional SQLite table: accepts incompatible data silently
CREATE TABLE legacy_inventory (
  sku TEXT,
  quantity INTEGER
);
-- Succeeds! Stores 'out of stock' as TEXT in quantity:
INSERT INTO legacy_inventory VALUES ('SKU-100', 'out of stock');

-- Modern SQLite STRICT table (SQLite 3.37+)
CREATE TABLE strict_inventory (
  sku TEXT NOT NULL,
  quantity INT NOT NULL
) STRICT;

-- FAILS with: Runtime error: datatype mismatch (code 20)
INSERT INTO strict_inventory VALUES ('SKU-100', 'out of stock');

Allowed data types in SQLite STRICT tables

Unlike traditional SQLite where any arbitrary type string like `VARCHAR(255)` or `DATETIME` is converted to an affinity, STRICT tables only permit five canonical keywords:

INT: Signed integer (up to 8 bytes). Rejects floating points and strings that cannot be losslessly converted.

REAL: 8-byte floating point number.

TEXT: UTF-8 string literal.

BLOB: Binary large object (stored exactly as provided).

ANY: Allows any SQLite data type (preserves dynamic typing for selected columns).

Well-formed SQLite STRICT table definition

CREATE TABLE app_users (
  id INTEGER PRIMARY KEY, -- Rowid alias
  username TEXT NOT NULL,
  score INT DEFAULT 0,
  balance REAL DEFAULT 0.0,
  avatar BLOB,
  metadata ANY
) STRICT;

Common mistakes to avoid

Using `VARCHAR` or `DATETIME` in a STRICT table: SQLite will throw a syntax error during table creation because only INT, REAL, TEXT, BLOB, and ANY are permitted.

Inserting empty strings `''` into numeric columns expecting them to default or convert to zero.

Assuming SQLite STRICT tables validate date formats: dates stored as ISO strings must still use TEXT.

SQLite "Datatype Mismatch" (Error 20): Type Affinity and Strict Tables

Quick answer

FAQs

Why does SQLite normally allow inserting text into an INTEGER column?

What are SQLite STRICT tables?

How do I create a STRICT table in SQLite?

Can a CHECK constraint cause SQLITE_MISMATCH?

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

PostgreSQL statement_timeout (Error 57014): Causes and Prevention

SQL Server "String or Binary Data Would Be Truncated": Fix Msg 8152 & 2628

Key takeaways

Type affinity versus STRICT tables in action

Allowed data types in SQLite STRICT tables

Common mistakes to avoid

Official references

Quick answer

FAQs

Why does SQLite normally allow inserting text into an INTEGER column?

What are SQLite STRICT tables?

How do I create a STRICT table in SQLite?

Can a CHECK constraint cause SQLITE_MISMATCH?

Keep reading

Fix BigQuery "Unrecognized Name" Error: Scoping and Syntax Rules

PostgreSQL statement_timeout (Error 57014): Causes and Prevention

SQL Server "String or Binary Data Would Be Truncated": Fix Msg 8152 & 2628

Key takeaways

Type affinity versus STRICT tables in action

Allowed data types in SQLite STRICT tables

Common mistakes to avoid

Related SQL guides

Official references