STRICT Tables: Rigid Type Enforcement Where You Want It

Learning outcomes

SQLite’s flexible typing predates STRICT tables by many years. STRICT tables were added for developers who want SQLite’s embedded architecture but prefer a stronger table-level type contract. Strictness is not a database-wide mode: each table opts in independently, allowing flexible staging tables and rigid application tables to coexist in the same database.

STRICT tightens a table without changing SQLite’s entire type system

A STRICT table uses the same database file format and the same five runtime storage classes you learned earlier. The difference is validation. Every column must have an allowed declared type, and values for typed columns must be NULL when permitted or be representable in the specified type after SQLite’s normal coercion attempt.

DROP TABLE IF EXISTS strict_reading;CREATE TABLE strict_reading (    reading_id  INTEGER PRIMARY KEY,    device_code TEXT NOT NULL,    reading     REAL,    raw_value   ANY) STRICT;PRAGMA table_list('strict_reading');PRAGMA table_xinfo('strict_reading');

On modern SQLite, PRAGMA table_list includes a strict flag and should report 1 for this table. Other tables in the same database remain ordinary unless their own CREATE statement ends with STRICT.

STRICT accepts a deliberately small declared-type vocabulary

Current STRICT tables accept only six type names. This avoids the surprising substring-derived type-name universe of ordinary tables and gives schema review a much clearer contract.

-- These names are not allowed as STRICT declared types:CREATE TABLE bad_boolean(flag BOOLEAN) STRICT;CREATE TABLE bad_varchar(name VARCHAR(40)) STRICT;CREATE TABLE bad_decimal(amount DECIMAL(10,2)) STRICT;

Run those examples only in a disposable lab. Each CREATE should fail because the declared type is not in the STRICT vocabulary. Model Boolean, date/time, decimal money, and UUID semantics using one of the allowed physical representations plus constraints/application rules, which Lesson 4 develops.

STRICT still allows useful lossless coercion

“Strict” does not mean “the incoming host value must already have exactly the final storage class.” SQLite still applies its usual conversion behavior. If the conversion preserves the value appropriately for the target type, the insert can succeed.

DROP TABLE IF EXISTS strict_contract;CREATE TABLE strict_contract (    id       INTEGER PRIMARY KEY,    count_n  INTEGER NOT NULL,    ratio    REAL NOT NULL,    label    TEXT NOT NULL) STRICT;INSERT INTO strict_contract(count_n, ratio, label)VALUES ('12', '0.5', 9001);SELECT count_n, typeof(count_n),       ratio,   typeof(ratio),       label,   typeof(label)FROM strict_contract;

The numeric-looking text can become numeric, and the integer literal used for the TEXT column can become text. The point of STRICT is not to disable conversion; it is to reject a value when SQLite cannot produce the required datatype without an unacceptable conversion.

Compare the same bad input in flexible and STRICT tables

The simplest way to understand the contract is to submit the same write to two schemas. The flexible table has INTEGER affinity; the strict table requires a valid INTEGER value.

DROP TABLE IF EXISTS flexible_count;DROP TABLE IF EXISTS strict_count;CREATE TABLE flexible_count(value INTEGER);CREATE TABLE strict_count(value INTEGER) STRICT;INSERT INTO flexible_count(value) VALUES ('12');INSERT INTO strict_count(value)   VALUES ('12');-- Flexible table keeps this as TEXT because it cannot become an integer.INSERT INTO flexible_count(value) VALUES ('twelve');-- Run separately: this should fail with a datatype constraint error.INSERT INTO strict_count(value) VALUES ('twelve');

After the successful statements, query typeof(value). Both tables store the convertible '12' as INTEGER 12. Only the flexible table accepts 'twelve' and stores it as TEXT. The STRICT insert is rejected; exact wrapper/CLI wording can vary, but the underlying condition is an SQLite datatype constraint failure.

ANY means intentional heterogeneity—and behaves differently under STRICT

ANY is the escape hatch for a column that intentionally stores more than one storage class. In a STRICT table, ANY preserves the received value and datatype. In an ordinary table, a declared type name of ANY falls through to NUMERIC affinity, so numeric-looking text may be converted.

DROP TABLE IF EXISTS flexible_any;DROP TABLE IF EXISTS strict_any;CREATE TABLE flexible_any(value ANY);CREATE TABLE strict_any(value ANY) STRICT;INSERT INTO flexible_any(value) VALUES ('000123');INSERT INTO strict_any(value)   VALUES ('000123');SELECT 'flexible' AS table_kind, value, typeof(value)FROM flexible_anyUNION ALLSELECT 'strict', value, typeof(value)FROM strict_any;

Expected behavior: the ordinary ANY column stores INTEGER 123 because its type name gets NUMERIC affinity, while the STRICT ANY column preserves TEXT 000123. This makes STRICT ANY useful for payloads where preserving exact incoming representation is part of the contract.

NULL, primary keys, and constraints still matter

STRICT typing does not replace relational constraints. A STRICT TEXT column can still accept NULL unless it is NOT NULL. A STRICT INTEGER column can still accept values outside your business range unless a CHECK or related rule rejects them. Type correctness and domain correctness are separate questions.

DROP TABLE IF EXISTS strict_device_state;CREATE TABLE strict_device_state (    device_id INTEGER PRIMARY KEY,    code      TEXT NOT NULL UNIQUE,    retries   INTEGER NOT NULL CHECK (retries BETWEEN 0 AND 10),    status    TEXT NOT NULL CHECK (status IN ('online','offline'))) STRICT;INSERT INTO strict_device_state(code, retries, status)VALUES ('PUMP-007', 3, 'online');-- Type-valid but domain-invalid: CHECK rejects it.INSERT INTO strict_device_state(code, retries, status)VALUES ('FAN-014', 99, 'online');

STRICT checks storage type compatibility. CHECK, UNIQUE, NOT NULL, PRIMARY KEY, and foreign-key rules express other invariants. Chapter 5 develops those constraints fully.

integrity_check and quick_check gain a STRICT type responsibility

PRAGMA integrity_check and PRAGMA quick_check are broader database-validation tools that Chapter 5 and Chapter 16 revisit. For STRICT tables, current SQLite also checks that stored values have the correct datatype and reports problems if the invariant has somehow been violated.

PRAGMA integrity_check;PRAGMA quick_check;

On a healthy lab database, each should return ok. Under normal modern operation, the STRICT write path prevents incompatible values from entering in the first place. The additional integrity check matters for corruption, unusual recovery scenarios, or databases that have crossed incompatible tooling boundaries.

Choose STRICT where it improves the application contract

STRICT is strongest when a table represents stable application-owned data whose physical representations are part of an interface. Flexible typing remains useful for ingestion, compatibility, or intentionally heterogeneous data. A database can use both approaches.

Lab: a FieldNotes API event contract

Create one flexible staging table and one STRICT final table. The staging table preserves questionable source values. The final table forces a stable envelope while allowing an intentionally heterogeneous payload.

DROP TABLE IF EXISTS api_event_staging;DROP TABLE IF EXISTS api_event;CREATE TABLE api_event_staging (    event_code,    sequence_no,    received_at,    payload);CREATE TABLE api_event (    event_id     INTEGER PRIMARY KEY,    event_code   TEXT NOT NULL,    sequence_no  INTEGER NOT NULL CHECK (sequence_no >= 0),    received_at  TEXT NOT NULL,    payload      ANY) STRICT;INSERT INTO api_event_staging VALUES    ('evt-001', '7', '2026-08-12T05:30:00Z', '000123'),    ('evt-002', 'unknown', '2026-08-12T05:31:00Z', x'00FF');-- Good conversion from staging into the final contract:INSERT INTO api_event(event_code, sequence_no, received_at, payload)SELECT event_code, sequence_no, received_at, payloadFROM api_event_stagingWHERE event_code='evt-001';SELECT event_code,       sequence_no, typeof(sequence_no),       payload, typeof(payload)FROM api_event;

The first staged sequence '7' can be converted losslessly to INTEGER 7. The STRICT ANY payload preserves the staged TEXT value as it arrives at that table. If you attempt to move 'unknown' into sequence_no, the final insert fails rather than turning a bad contract into hidden TEXT.

Failure patterns and safe corrections

Summary and bridge to logical types

STRICT tables give SQLite a deliberate per-table rigid contract without changing its five runtime storage classes. You now know the allowed type names, lossless-conversion behavior, special STRICT ANY semantics, compatibility floor, and integrity-check role. The next problem is practical: many application concepts—dates, booleans, exact money, UUIDs—are not storage classes at all. You must choose representations for them.

References

• SQLite — STRICT Tables.
• SQLite — Datatypes In SQLite.
• SQLite — Result and Error Codes.
• SQLite — PRAGMA Statements.
• SQLite — CREATE TABLE.
• SQLite — Release History.