Data types, affinity, STRICT tables and WITHOUT ROWID

How type affinity really works, why STRICT tables are worth adopting, rowid versus primary key, and generated columns.

Affinity, not types

create table legacy (v);            -- affinity: BLOB, nothing is converted
create table a (n integer);         -- INTEGER affinity: '42' is stored as 42
create table b (t text);            -- TEXT affinity: 42 is stored as '42'
create table c (r real);            -- REAL affinity
create table d (x numeric(10,2));   -- NUMERIC affinity

insert into a (n) values ('42'), ('4.5'), ('abc');
select n, typeof(n) from a;
-- 42    integer
-- 4.5   real      (numeric text that looks like a number is converted)
-- abc   text      (not numeric, so the value is stored as given)

• A column's declared type decides affinity, which decides conversion. It does not constrain what can be stored.
• NUMERIC affinity converts text that looks numeric, and stores text unchanged when it does not - that is why a 'text in a numeric column' bug appears only for some rows.
• typeof() reports the storage class of a specific value; the declared type does not appear anywhere at runtime.
• Because affinity is per value, where n = '42' and where n = 42 behave differently on an indexed column. Always bind parameters with the intended type.

STRICT tables

create table book (
  id        integer primary key,
  title     text not null,
  isbn      text unique,
  pages     integer check (pages > 0),
  price     real,
  cover     blob,
  published text,
  data      any
) strict;

-- now these fail instead of being silently accepted
insert into book (title, pages) values ('A', 'many');   -- datatype mismatch
insert into book (id, title) values (1, 42);            -- datatype mismatch

• ANY is the escape hatch: the column accepts any storage class and is not converted, which is what a JSON blob column often wants.
• STRICT does not make INTEGER PRIMARY KEY stop being the rowid alias - it is still the fast, compact key.
• Converting an existing table means creating a new STRICT table and copying rows; run cast() in the copy and fix the rows that fail rather than hoping they convert.
• Foreign keys, CHECK constraints and NOT NULL work exactly as before; STRICT only adds type enforcement.

-- migration to a STRICT table, preserving the rowid
begin;
create table book_new (
  id integer primary key, title text not null, pages integer
) strict;
insert into book_new (id, title, pages)
  select id, title, cast(pages as integer) from book;
drop table book;
alter table book_new rename to book;
commit;

rowid, WITHOUT ROWID and generated columns

create table tag (
  name text primary key,
  usage integer not null default 0
) without rowid, strict;

-- WITHOUT ROWID: the primary key is the table, no separate index lookup
-- good for small keys, narrow rows, and lookups by primary key
-- bad when the key is wide (it is stored in every index)

create table invoice (
  id integer primary key,
  net   integer not null,
  rate  real not null default 0.2,
  gross integer generated always as (cast(net * (1 + rate) as integer)) stored,
  slug  text generated always as (lower(replace(title, ' ', '-'))) virtual,
  title text not null
);

select id, title, net, gross from invoice;

• integer primary key is an alias for the rowid: no extra index, and inserting NULL assigns the next value automatically.
• A rowid is not guaranteed to be contiguous or monotonic. Deletes create gaps and a reused id can appear unless AUTOINCREMENT is used, which adds a table to track the maximum.
• A STORED generated column is computed at write time and can be indexed; a VIRTUAL one is computed on read and cannot be part of an index expression unless you index the expression itself.
• WITHOUT ROWID tables are faster for point lookups by primary key and use less space for narrow rows, but every secondary index duplicates the wide primary key.

FAQ

Related

The database in a file, and the sqlite3 CLI Querying with SQL: joins, grouping, upserts and CTEs

Last refreshed 2026-09-18.