In SQLite, what does declaring a table STRICT change about typing, and what does it cost you on older SQLite versions?
answer
- one keyword after the closing parenthesis
- six allowed type names
- coerce if lossless, else fail
- ANY keeps values exactly
- older libraries cannot read it
basics
~20 sA STRICT table (SQLite 3.37.0 and later) allows only INT, INTEGER, REAL, TEXT, BLOB or ANY columns, converts a value only when lossless, and otherwise fails with SQLITE_CONSTRAINT_DATATYPE. Older libraries report an error on such a schema.
solid answer
~40 sAdding `STRICT` after the closing parenthesis of `CREATE TABLE` (SQLite 3.37.0 and later) turns on rigid typing for that one table. Every column must declare one of `INT`, `INTEGER`, `REAL`, `TEXT`, `BLOB` or `ANY`. On each write SQLite still tries the usual affinity conversion, so `'42'` becomes 42 in an INTEGER column, but a value that cannot be converted losslessly, such as `'n/a'` or 3.5, fails with `SQLITE_CONSTRAINT_DATATYPE`. `ANY` keeps every value exactly as given, and PRIMARY KEY columns become NOT NULL. The cost is compatibility: a library older than 3.37.0 reports an error on the schema, and a phone's built-in SQLite can be that old. An existing table cannot be switched with ALTER TABLE; it has to be rebuilt.
code
sql · 6 linesCREATE TABLE prices(sku TEXT NOT NULL, qty INTEGER, note ANY) STRICT;
INSERT INTO prices VALUES ('A-100', '42', '000123');
SELECT typeof(qty), quote(note) FROM prices;
-- integer|'000123'
INSERT INTO prices VALUES ('B-200', 'n/a', NULL);
-- fails: cannot store TEXT value in INTEGER column prices.qtygo deeper
Recall that STRICT is an opt-in per table, that only six type names are allowed, and that it needs SQLite 3.37.0 or newer.
Explain that STRICT still converts losslessly and fails otherwise with SQLITE_CONSTRAINT_DATATYPE, how ANY differs between strict and ordinary tables, and what stays unchanged.
Plan moving a messy table to STRICT: audit with typeof(), clean the strays, rebuild in one transaction, and confirm every device's SQLite is at least 3.37.0.
Decide whether STRICT becomes the team default for new SQLite schemas, weighing loud failures and portability against the oldest library version you must support.
## Why STRICT exists SQLite's ordinary tables use **flexible typing**: a column's declared type only sets a preferred storage class (its affinity), and a value that cannot be converted is stored anyway. Some teams rely on that freedom; others want the rigid behaviour of the SQL standard and of other engines. Since **SQLite 3.37.0** (2021-11-27) a table can opt in to rigid typing with the `STRICT` table option. The choice is made per table, so a database can mix strict and ordinary tables. ## What the keyword changes `STRICT` goes after the closing parenthesis of the column list, for example `CREATE TABLE prices(sku TEXT, qty INTEGER) STRICT;`. It can be combined with the other table option, `WITHOUT ROWID`, in a comma-separated list. It changes four things: - **Every column needs a type.** Leaving one out fails at `CREATE TABLE` with an error such as `missing datatype for prices.note`. - **Only six type names are allowed:** `INT`, `INTEGER`, `REAL`, `TEXT`, `BLOB` and `ANY`. A name like `VARCHAR(20)` or `DATETIME` fails at `CREATE TABLE`, for example with `unknown datatype for prices.sold_on: "DATETIME"`. - **Writes are checked.** SQLite first applies the ordinary affinity conversion, then checks the result. A value that is neither NULL nor of the declared type makes the statement fail with the extended result code `SQLITE_CONSTRAINT_DATATYPE` and a message like `cannot store TEXT value in INTEGER column prices.qty`. - **PRIMARY KEY columns are implicitly NOT NULL**, which removes the ordinary-table quirk that lets a non-integer primary key hold NULLs. Inserting NULL into an `INTEGER PRIMARY KEY` still assigns a fresh integer, as in ordinary tables. `PRAGMA integrity_check` and `PRAGMA quick_check` also verify the type of every value in STRICT tables. ## How values are treated STRICT does not mean "no conversion"; it means "convert losslessly or fail": | Value written | INTEGER column | REAL column | TEXT column | ANY column | |---|---|---|---|---| | text `'42'` | integer 42 | real 42.0 | text `'42'` | text `'42'` | | text `'n/a'` | error | error | text `'n/a'` | text `'n/a'` | | real 3.5 | error | real 3.5 | text `'3.5'` | real 3.5 | | real 3.0 | integer 3 | real 3.0 | text `'3.0'` | real 3.0 | | blob `x'00'` | error | error | error | blob | ## ANY: flexible typing on purpose `ANY` lets a STRICT table keep SQLite's ability to store any class in one column, but explicitly. In a STRICT table an `ANY` column preserves each value exactly: the text `'000123'` stays text. In an ordinary table, by contrast, a column declared `ANY` is just a type name that falls through to NUMERIC affinity, so the same text is stored as the integer 123. ## What stays the same CHECK, NOT NULL, UNIQUE and FOREIGN KEY constraints, DEFAULT and COLLATE clauses, generated columns, indexes, `ON CONFLICT` clauses and `AUTOINCREMENT` all work as in ordinary tables. `INTEGER PRIMARY KEY` is still an alias for the rowid, while `INT PRIMARY KEY` is not. The on-disk format of the table data is identical. ## The costs 1. **Older libraries.** The `STRICT` keyword is recognised only by 3.37.0 and later; an older library reports an error on a schema that contains it. So a file with a STRICT table generally needs 3.37.0 or newer to be read or written. A phone's built-in SQLite lags the current release, so check `sqlite_version()` on the oldest device you support, or ship your own build of the library. 2. **A workaround that disables the guarantee.** An older library can be made to open the file by setting `PRAGMA writable_schema=ON` first, which makes the parser ignore the keyword. Types are then not enforced, wrong values can be written, and a later `PRAGMA quick_check` on a newer library reports them as corruption. 3. **No in-place conversion.** ALTER TABLE cannot make an existing table STRICT. You create a new STRICT table, copy the rows across and rename it into place, and any row that fails conversion stops the copy, so clean the data first. ## When it is worth it STRICT suits new schemas whose application code assumes rigid types, imports that should fail loudly rather than store junk, and schemas meant to move to another engine later. It is a poor fit for a database file that must still open on libraries older than 3.37.0.
- In a SQLite STRICT table, is a column declared INT PRIMARY KEY an alias for the rowid?No. As in ordinary tables, only a single-column primary key declared exactly `INTEGER` becomes the rowid alias. `INT PRIMARY KEY` is an ordinary INT column with a unique index beside the hidden rowid, so it costs an extra index. Use `INTEGER PRIMARY KEY` when you want the rowid itself.
- How do you move an existing SQLite table to STRICT typing?Rebuild it: create the new table with `STRICT`, copy the rows with `INSERT INTO ... SELECT`, drop the old table and rename the new one into place, all in one transaction, then recreate its indexes, triggers and views. Clean values first, because any row that cannot be converted losslessly makes the copy fail.
- A STRICT table rejects the text '42' in an INTEGER column
- STRICT tables accept any type name, such as VARCHAR(20)
- ALTER TABLE can switch an existing SQLite table to STRICT
- An ANY column in an ordinary table keeps '000123' as text
- STRICT tables use a different file format from ordinary tables
Assumes SQLite 3.53; STRICT tables exist only from 3.37.0 (2021-11-27), and older libraries report an error on a schema that uses them.