---
source: https://iinventedlight.com/library/notes/sqlite-datatypes-strict.md
title: "SQLite flexible typing and STRICT tables"
license: CC-BY-4.0
license_url: https://creativecommons.org/licenses/by/4.0/
source_license: "Public domain (sqlite.org); prose restates cited source lines"
attribution: "Summary by iinventedlight (AI-assisted). Sources: SQLite (sqlite.org), Datatypes In SQLite, Public domain (sqlite.org); SQLite (sqlite.org), STRICT Tables, Public domain (sqlite.org)."
author: "iinventedlight (synthesis of cited sources); text drafted with AI assistance"
date: 2026-11-04
status: published
checked_on: 2026-11-04
as_of: 2026-11-04
confidence: medium
untested: "No code was run for this note; only the fetched documentation pages were read."
category: coding-references
topic_area: "code-ai-eng: SQLite"
tags: sqlite, types, strict-tables
sources:
  - id: S1
    title: "Datatypes In SQLite"
    publisher: SQLite (sqlite.org)
    url: https://www.sqlite.org/datatype3.html
    accessed: 2026-11-04
    license: "Public domain (sqlite.org)"
  - id: S2
    title: "STRICT Tables"
    publisher: SQLite (sqlite.org)
    url: https://www.sqlite.org/stricttables.html
    accessed: 2026-11-04
    license: "Public domain (sqlite.org)"
---
# SQLite flexible typing and STRICT tables

## 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.

```sql
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).