NOETRION

Choose a topic

DATA WORKFLOW · 6 MIN READ

SQLite STRICT Tables: Types and Domain Constraints Solve Different Problems

Test SQLite type coercion, null checks, unique keys, and domain rules without assuming that STRICT validates every input requirement.

By NoetrionSources checked How this guide was prepared

Reviewed October 8, 2026. Examples use Python 3.12 and SQLite 3.53.1; STRICT requires SQLite 3.37.0 or later.

A table declares a duration as an integer. Does that mean it rejects the string "5", rejects negative values, and requires every row to supply a duration? Those are three separate questions. In a SQLite STRICT table, a numeric string can be converted losslessly to an integer; a negative integer can still satisfy the type; and a nullable column can still accept an absent value.

Reliable schema design combines storage types with domain rules. This guide demonstrates that distinction using a synthetic study-session table, then explores null behavior, uniqueness, and values whose original representation must be preserved. The goal is to know exactly which invalid states the database prevents and which input rules remain the application's responsibility.

1. Declare the supported storage types

A STRICT table requires each column to declare one of its supported type names, including INTEGER, REAL, TEXT, BLOB, and ANY, with INT also supported. This differs from ordinary SQLite's flexible typing. It provides a useful storage guarantee, but not a full application schema language with dedicated boolean, date, or domain-specific types.

For a non-ANY column, SQLite attempts an appropriate lossless conversion. A value supplied as textual digits may therefore be accepted and stored as an integer. A value that cannot be converted losslessly is rejected. If the application requires an original Python integer and forbids textual input, enforce that before binding values to SQL. The database's accepted storage value and the caller's accepted input representation are different contracts.

STRICT also leaves ordinary constraints meaningful. NOT NULL, CHECK, UNIQUE, and foreign keys continue to express separate rules. Type enforcement does not establish that a quantity is nonnegative, that a name is nonblank, or that a reference exists in another table.

Storage typing and domain constraints are distinct schema layers
An original diagram. STRICT can accept a losslessly convertible numeric string; CHECK and NOT NULL must still express value and presence rules. Open full-size diagram.

2. Test a complete small schema

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:", isolation_level=None)) as con:
    con.execute("""CREATE TABLE session(
        id INTEGER PRIMARY KEY,
        label TEXT NOT NULL UNIQUE CHECK(length(trim(label)) > 0),
        minutes INTEGER NOT NULL CHECK(minutes BETWEEN 0 AND 240)
    ) STRICT""")
    con.execute("INSERT INTO session(label, minutes) VALUES (?, ?)",
                ("valid", "5"))
    assert con.execute("SELECT minutes, typeof(minutes) FROM session").fetchone() == (5, "integer")
    failures = [("bad_text", "five"), ("negative", -1),
                ("missing", None), ("fraction", 5.5),
                ("   ", 10), ("valid", 20)]
    for label, minutes in failures:
        try:
            con.execute("INSERT INTO session(label, minutes) VALUES (?, ?)",
                        (label, minutes))
        except sqlite3.IntegrityError:
            continue
        raise AssertionError((label, minutes, "must be rejected"))
    assert con.execute("SELECT COUNT(*) FROM session").fetchone() == (1,)
    print("Numeric text was coerced; six invalid records were rejected.")

The accepted string "5" becomes the integer five. Text that cannot represent an integer and a nonintegral real value fail the storage-type contract. The negative integer passes the type question but fails the range question. The null duration fails required presence. A whitespace-only label fails the example's blankness rule, and a repeated valid label fails uniqueness.

The label check intentionally uses SQLite's ordinary trim behavior, not a universal definition of Unicode whitespace. A tab-only or other unusual text input needs additional rules if the domain rejects it. A small SQL expression should not be advertised as validating every possible human name or every textual blank representation.

Rule Example it rejects What it does not decide
STRICT INTEGER five or 5.5 Whether a negative integer is meaningful
NOT NULL Absent minutes Whether zero is permitted
CHECK range Minutes outside 0–240 Whether the input originally arrived as text
UNIQUE label Repeated non-null label Whether labels differing in case are equivalent
Foreign key Unsupported related key when enforced Whether the related field may be null

3. A CHECK expression can allow NULL

A CHECK fails when its expression evaluates to zero. A null result is not a violation. Therefore CHECK(minutes >= 0) without NOT NULL does not require minutes to be present. This behavior follows SQL's handling of unknown comparisons rather than an integer-type bug.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:", isolation_level=None)) as con:
    con.execute("CREATE TABLE optional(minutes INTEGER CHECK(minutes >= 0)) STRICT")
    con.execute("INSERT INTO optional VALUES (NULL)")
    assert con.execute("SELECT minutes FROM optional").fetchone() == (None,)
    con.execute("CREATE TABLE labels(label TEXT UNIQUE) STRICT")
    con.execute("INSERT INTO labels VALUES (NULL)")
    con.execute("INSERT INTO labels VALUES (NULL)")
    assert con.execute("SELECT COUNT(*) FROM labels").fetchone() == (2,)
    print("Nullable CHECK and nullable UNIQUE both permit these null rows.")

The second table also permits multiple null labels under SQLite's uniqueness behavior. If every row needs a unique supplied label, combine UNIQUE with NOT NULL. Do not infer a required value merely because a uniqueness constraint is present. Presence, comparison semantics, and uniqueness are independent parts of the domain.

An INTEGER PRIMARY KEY has additional rowid behavior: inserting NULL can allocate a new integer key. That is useful for generated identifiers, but different from a rule that a caller must supply an ID. A schema designer should decide whether identifiers are database-generated or externally assigned and test that contract directly.

4. Preserve original representation where it matters

If a code "0005" must retain leading zeros, declaring its destination as an integer contradicts that requirement. TEXT often expresses the intended representation more clearly. Validate the textual code's shape separately from its storage type.

The STRICT ANY type can preserve the supplied SQLite value and type without coercion. It is useful for deliberately heterogeneous raw staging fields, but does not imply the field is ready for analysis. Keep the source representation, then produce a separately validated typed field when the workflow needs one.

import sqlite3
from contextlib import closing

with closing(sqlite3.connect(":memory:")) as con:
    con.execute("CREATE TABLE raw_value(value ANY) STRICT")
    con.execute("INSERT INTO raw_value VALUES (?)", ("0005",))
    con.execute("INSERT INTO raw_value VALUES (?)", (5,))
    rows = con.execute("SELECT value, typeof(value) FROM raw_value ORDER BY rowid").fetchall()
    assert rows == [("0005", "text"), (5, "integer")]
    print(rows)

The two rows may refer to different domain values even though integer conversion could make them compare similarly. A staging design should retain record identity and transformation reasons alongside raw values. Flexible staging and strict curated tables can complement each other when the boundary is explicit.

5. Schema guarantees depend on connection and operation policy

Foreign keys still require verified enforcement settings. Constraints also cannot repair a database file modified outside SQLite's supported operations. Integrity checks can detect some storage problems, but they are not a substitute for correct application writes, permissions, backups, and migration procedures.

Decide how the application handles rejection. Returning a clear validation error can be appropriate for one interactive row. A batch import may instead keep a rejection audit and roll back the complete batch, or accept valid rows under an explicit partial-import contract. The database exception establishes a violation; the application decides the unit of recovery.

Avoid silently using conflict policies that discard rows to make an import finish. Reconcile requested, accepted, and rejected records. A final count can look plausible even after meaningful records were skipped. Constraints are most useful when failure remains observable and connected to the originating input.

6. Test both accepted coercions and rejected values

A good schema test includes zero, the maximum allowed duration, a negative value, a value above the maximum, a null, a fractional value, an unconvertible string, and a numeric string. Testing only obvious invalid text misses the distinction between storage type and original representation.

For practice, change the duration maximum to 180 and rerun boundary tests. Then decide whether "5" should be accepted by the application even though the database accepts it. Document both layers. A defensible schema is a set of specific guarantees with tested boundaries, not a general promise that every row in a STRICT table is correct.

Sources and scope

The study-session schema is synthetic and intentionally narrow. It is not a complete validation model for dates, names, or externally supplied identifiers.