Data Types in SQLiteBeginner
1Question 1 of 1
sqlWhat is SQLite's Type Affinity system and how does it differ from strict static SQL typing?
Explanation & Concept
SQLite uses dynamic typing with 'Type Affinity' rather than rigid static types. The datatype of a value is associated with the value itself, not with its column container.
SQLite recognizes 5 storage classes: 1. `NULL` 2. `INTEGER` 3. `REAL` (floating point) 4. `TEXT` 5. `BLOB` (binary data)
Columns have affinities (`TEXT`, `NUMERIC`, `INTEGER`, `REAL`, `BLOB`) that determine how incoming values are converted. In SQLite 3.37+, you can also opt into strict mode via `CREATE TABLE ... STRICT`.
Code Example
sql
-- Strict mode table (SQLite 3.37+)
CREATE TABLE strict_users (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
age INT,
registered_at TEXT
) STRICT;
-- In STRICT tables, inserting a string into 'age' will throw an error:
-- INSERT INTO strict_users VALUES (1, 'Alice', 'twenty', '2026-01-01'); -- Fails!How It Works & Key Takeaways
Traditional SQLite allows any datatype in any column (except `INTEGER PRIMARY KEY`). The `STRICT` table option brings standard relational type enforcement when desired.
Mock Test · 1 Questions · Optional
Ready for Indexes?
Take a quick test to evaluate yourself, or move straight to the next module.
Beginner1 questions