SQLite / Basic: the embedded engine and correct data / Lesson 2

Storage classes, affinity, and STRICT tables

Choose SQLite representations from domain meaning and distinguish flexible affinity conversion from the stronger guarantees of STRICT tables.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Explain value storage classes and column affinity.
Use STRICT with supported type names.
Represent money, dates, booleans, and identifiers deliberately.

A declaration and a stored value are different facts

SQLite values use storage classes NULL, INTEGER, REAL, TEXT, and BLOB. In an ordinary table, the declared column type usually determines affinity: a preference for converting compatible values, rather than a universal prohibition on other storage classes. typeof() inspects the stored value. A column named INTEGER can therefore contain text that could not be converted losslessly under ordinary affinity rules.

This flexibility is useful for some applications but can hide a broken input contract. A successful INSERT does not prove that a quantity is a valid number or that an identifier remained text. Validate the source meaning and inspect representative values. Affinity also affects comparisons and sorting, so mixed storage can change results beyond the initial insertion.

Observe conversion rather than guessing it

The first script inserts a number, numeric text, and nonnumeric text into an ordinary INTEGER-affinity column. Numeric text123 becomes an integer, while pear remains text. These rows show why a declared type alone is not a validation rule in an ordinary SQLite table. Explicit identifiers and ordering keep the observation reproducible.

Affinity is determined through documented type-name rules, not a complete catalog of rigid types from another database. DECIMAL commonly supplies NUMERIC affinity and does not create fixed decimal arithmetic or enforce a decimal scale. VARCHAR(20) does not by itself enforce twenty-character length. Use appropriate representations and CHECK constraints rather than importing assumptions from another SQL engine.

sql Inspect ordinary affinity conversion

CREATE TABLE flexible(id INTEGER PRIMARY KEY, value INTEGER);
INSERT INTO flexible VALUES(1,7),(2,'123'),(3,'pear');
SELECT id,value,typeof(value) AS storage FROM flexible ORDER BY id;

Expected output

id|value|storage
1|7|integer
2|123|integer
3|pear|text

The numeric string is converted, while the nonnumeric token remains TEXT. This table has no domain constraint rejecting it.

Use STRICT for a deliberate type boundary

STRICT tables were introduced in SQLite3.37.0. Their supported declared type names are INT, INTEGER, REAL, TEXT, BLOB, and ANY. Values must meet the table's stricter type rules after allowed lossless conversion; STRICT is not a ban on every conversion. ANY in a STRICT table preserves the supplied storage representation, useful when exact input representation is intentional.

The second script preserves003 as text and converts a valid quantity token12 to integer. A nonnumeric quantity would fail instead of being stored as arbitrary text. STRICT does not establish positive quantity, valid dates, safe authorization, or correct units. Those are additional domain and application contracts. Older runtimes cannot simply ignore the STRICT keyword when reading or creating the schema.

sql Preserve a label while enforcing a quantity type

CREATE TABLE precise(id INTEGER PRIMARY KEY, code TEXT NOT NULL,
 quantity INTEGER NOT NULL, raw ANY) STRICT;
INSERT INTO precise VALUES(1,'003','12','003');
SELECT code,quantity,typeof(quantity) AS quantity_type,raw,typeof(raw) AS raw_type
FROM precise ORDER BY id;

Expected output

code|quantity|quantity_type|raw|raw_type
003|12|integer|003|text

The code and ANY value retain003 as text, while quantity becomes integer12 through a permitted conversion.

Choose units and precision explicitly

For exact bounded money, integer minor units can avoid binary floating-point rounding, provided the currency, scale, range, and rounding boundary are documented. REAL is appropriate for many approximate measurements but is not exact decimal money. An INTEGER can overflow, so the host must validate accepted ranges and arithmetic as well as storage.

SQLite has no separate date/time or Boolean storage class. Dates can use a defined ISO-style TEXT convention, Julian-day REAL, or Unix-time INTEGER according to the application contract. A Boolean-style integer needs NOT NULL and CHECK(value IN (0,1)) when required. A timestamp's time-zone convention and an amount's currency remain essential even when the storage class is correct.

Keep text and missingness semantics honest

TEXT stores textual information, but normalization and comparison need their own policy. Built-in NOCASE mainly handles ASCII case folding and is not a universal Unicode language comparison. Arabic labels, accented names, and case-sensitive product codes should be tested under their actual intended rules. Length checks also need a stated unit and handling of embedded or unusual characters.

NULL means missing information, not zero or a blank label. A CHECK expression that evaluates to NULL is not a replacement for NOT NULL. Record the allowed absence states and test blank, zero, numeric-looking identifiers, malformed input, and boundary values. The right SQLite design makes flexible representation an explicit choice rather than an accidental invitation to inconsistent data.

Common errors and fixes

Nonnumeric text appears in a quantity column.

Why it happens: An ordinary affinity declaration was mistaken for strict validation.
How to fix it: Validate the input and choose STRICT plus domain constraints where appropriate.
Prevent it next time: Test typeof() and invalid tokens.

unknown datatype for a STRICT column

Why it happens: A type such as VARCHAR or DECIMAL was used in a STRICT definition.
How to fix it: Choose a supported type and express the required domain separately.
Prevent it next time: Check version and supported type names.

A monetary calculation has unexpected fractions.

Why it happens: REAL or NUMERIC affinity was assumed to provide exact decimal arithmetic.
How to fix it: Use a documented integer-scale or suitable host decimal design.
Prevent it next time: Test rounding and range boundaries.

Check your understanding

1. Why is an identifier003 usually TEXT even when INTEGER can parse it?

Show worked solution

Its leading zeros belong to identity and arithmetic may be meaningless. Preserve TEXT and validate the label format. A quantity field is a different concept and should have its own type, range, and unit.

2. Does STRICT INTEGER alone guarantee a required positive quantity?