← All notes

Short answer

In SQLite a value's type belongs to the value, not the column. A column type is only an "affinity": inserting '123' into an INTEGER column stores the number 123, but 'xyz' is stored as text.

CREATE TABLE t (id INTEGER PRIMARY KEY, n INTEGER, note ANY) STRICT;

Key points

• In a STRICT table, inserting a value of the wrong type raises SQLITE_CONSTRAINT_DATATYPE. [S2]
• A column typed ANY still accepts any value. [S2]

Claims

• id: C1 claim: SQLite uses a dynamic type system. source: S1 evidence: "SQLite uses a more general dynamic type system."
• id: C2 claim: The datatype is associated with the value, not its container. source: S1 evidence: "of a value is associated with the value itself, not with its container."
• id: C3 claim: Wrong-type content in a STRICT table raises SQLITE_CONSTRAINT_DATATYPE. source: S2 evidence: "SQLITE_CONSTRAINT_DATATYPE error is raised."
• id: C4 claim: SQLite strives to be flexible about datatypes. source: S2 evidence: "SQLite strives to be flexible regarding the datatype of"

Sources

• [S1] Datatypes In SQLite. SQLite (sqlite.org). https://www.sqlite.org/datatype3.html . License: Public domain (sqlite.org). Accessed 2026-11-04.
• [S2] STRICT Tables. SQLite (sqlite.org). https://www.sqlite.org/stricttables.html . License: Public domain (sqlite.org). Accessed 2026-11-04.

License: this note is CC BY 4.0 (https://creativecommons.org/licenses/by/4.0/). Summary by iinventedlight (AI-assisted).

@misc{iil_note_3w0rcbjz,
  author = {{iinventedlight}},
  title = {{SQLite flexible typing and STRICT tables}},
  howpublished = {iinventedlight Library},
  year = {2026},
  date = {2026-11-04},
  url = {https://iinventedlight.com/library/notes/sqlite-datatypes-strict/},
  note = {Permanent ID iil:note:3w0rcbjz. License: CC-BY-4.0.},
}

Markdown

Spotted a mistake? Use the MCP feedback tool or email hello@iinventedlight.com.

For language models and agents: start at /llms.txt. This page in Markdown: /library/notes/sqlite-datatypes-strict.md.