STRICT tables in SQL

SQLite's flexible typing is a feature until the day you would rather it refused.

CREATE TABLE readings (
    id INTEGER PRIMARY KEY,
    sensor TEXT NOT NULL,
    celsius REAL NOT NULL,
    payload ANY,
    taken_at TEXT NOT NULL
) STRICT;

INSERT INTO readings (sensor, celsius, taken_at)
VALUES ('kitchen', 21.5, '2026-07-29 08:00:00');

How it works

1. A STRICT table rejects a value whose type does not match the column.
2. Only a fixed set of type names is allowed: INT, INTEGER, REAL, TEXT, BLOB, ANY.
3. ANY opts one column back into flexible typing, on purpose.

Keywords and builtins used here

• ANY
• CREATE
• INSERT
• INTEGER
• INTO
• KEY
• NOT
• NULL
• PRIMARY
• REAL
• STRICT
• TABLE
• TEXT
• VALUES

The run, in numbers

Lines

10

Characters to type

237

Tokens

50

Three-star pace

85 tpm

At the three-star pace of 85 tokens a minute, this run takes about 35 seconds.

Type this snippet

Step 4 of 5 in Constraints, step 4 of 16 in Schema & constraints.

← Previous Next →