Type Affinity — SQLite's Flexible Typing

The 'lying about types' feature

By default, SQLite uses type affinity, not strict typing. A column declared INTEGER will prefer integer storage but will accept a string. A TEXT column will accept a number. This is intentional — it makes SQLite easy to embed in places where data shape is fluid (e.g., legacy CSV imports).

The five affinity classes:

INTEGER — column types containing 'INT'.
TEXT — types containing 'CHAR', 'CLOB', or 'TEXT'.
BLOB — no affinity (column declared with no type, or with 'BLOB').
REAL — types containing 'REAL', 'FLOA', or 'DOUB'.
NUMERIC — anything else, including the ambiguous 'NUMERIC' name itself.

Affinity rules are quirky. Most of the time you can ignore them and just declare what you mean. But when surprises happen — a SUM coming back as a string, an index missing a row — the answer is almost always 'a value snuck in with the wrong storage class'.

Warning: Type affinity also affects how indexes and comparisons work. WHERE id = '42' behaves differently than WHERE id = 42 if id has TEXT affinity. The fix is either STRICT mode (next lesson) or careful Python-side typing.

Code

Affinity in action — same column, different storage classes·sql

CREATE TABLE demo (val INTEGER);
INSERT INTO demo(val) VALUES (1);     -- stored as integer
INSERT INTO demo(val) VALUES ('two'); -- stored as text — SQLite accepts it!
INSERT INTO demo(val) VALUES (3.14);  -- stored as real

SELECT val, typeof(val) FROM demo;
-- 1   | integer
-- two | text
-- 3.14| real

How to defend against affinity surprises·sql

-- Option 1: CHECK constraint
CREATE TABLE strict_int (
  v INTEGER NOT NULL CHECK (typeof(v) = 'integer')
);

INSERT INTO strict_int(v) VALUES ('hi');
-- Error: CHECK constraint failed: typeof(v) = 'integer'

-- Option 2: STRICT tables (3.37+) — covered next lesson
CREATE TABLE really_strict (v INTEGER NOT NULL) STRICT;

Exercise

Build a small demo table and try inserting wrong-typed values into a column. Use typeof(col) in a SELECT to see what storage class SQLite picked. Then add a CHECK constraint to refuse the wrong type, retest, and observe the error. Explain in your notes why type affinity is sometimes useful and sometimes a footgun.

Type Affinity — SQLite's Flexible Typing

The 'lying about types' feature

Code

External links

Exercise

Progress

Comments 0