Skip to content
Tutorials Center
My learning

Topics

SQL Reference ยท Constraints and Data Types

SQLite type affinity

How SQLite type affinity works: the five affinities, the rules that map a declared type name to one, automatic conversion on insert, and STRICT tables.

In most databases a column's type is a rule: put text into an INTEGER column and you get an error. SQLite works differently. Every value carries its own storage class (NULL, INTEGER, REAL, TEXT or BLOB), and a column's declared type only sets a type affinity, a preference for how incoming values are converted. This design is flexible, and it surprises people who come from other databases.

This page explains the rules, because they decide what typeof() reports and how comparisons and sorting behave in SQLite and in this site's runnable examples.

The five affinities

SQLite looks at the declared type name and applies these rules in order:

If the name contains... Affinity
INT INTEGER
CHAR, CLOB or TEXT TEXT
BLOB, or no type at all BLOB (also called NONE)
REAL, FLOA or DOUB REAL
anything else NUMERIC

Because it is substring matching, BIGINT, UNSIGNED INT and INT8 are all integer affinity; VARCHAR(20) is text; DECIMAL(10,2), BOOLEAN and DATE fall through to numeric. The length in parentheses is ignored.

What an affinity does on insert

  • INTEGER and NUMERIC: text that looks like a number is converted to an integer or real. A real that equals a whole number (3.0) becomes an integer. Text that is not a number stays text.
  • REAL: integers are stored as floating point.
  • TEXT: numbers are converted to their text form.
  • BLOB: nothing is converted.
SQL Same inputs, different affinities

Press Run (or Ctrl + Enter) to see the result here.

The first row shows the idea: the text '42' becomes the integer 42 in the INTEGER and NUMERIC columns and a real in the REAL column, while the TEXT column turns the number 42 into text and the BLOB column keeps whatever it was given. In the last row, 'abc' is not a number, so every column keeps it as text. SQLite never complains.

Names that match the wrong rule

Because the rules are substring checks, a name can land in an affinity you did not expect. FLOATING POINT contains INT, so it gets INTEGER affinity, and STRING contains none of the keywords, so it gets NUMERIC.

SQL Which affinity does this name get?

Press Run (or Ctrl + Enter) to see the result here.

STRICT tables

Since SQLite 3.37 you can opt in to ordinary type rules by adding STRICT after the column list. Strict tables only accept the types INT, INTEGER, REAL, TEXT, BLOB, ANY, and they reject values that cannot be converted, rather than keeping them as text.

SQL STRICT rejects bad values (fails on purpose)

Press Run (or Ctrl + Enter) to see the result here.

The conversion of '12' still happens (strict tables apply the affinity rules too), but 'twelve' fails with cannot store TEXT value in INTEGER column strict_demo.qty.

How affinity affects comparisons and sorting

  • A column with numeric affinity compared to a text literal converts the literal first, so WHERE qty = '12' matches the integer 12.
  • With no affinity involved, values of different storage classes never compare equal: 1 = '1' is false.
  • Sorting puts storage classes in a fixed order: NULL, then numbers, then text, then blobs. A mixed column sorts predictably but not usefully.

Habits for portable schemas

  • Write ordinary type names anyway (INTEGER, TEXT, REAL) so the schema moves to other databases.
  • Add CHECK constraints (CHECK (typeof(qty) = 'integer')) or use STRICT when you need type safety.
  • Use CAST and TYPEOF() when you debug unexpected comparisons.
  • Remember that PostgreSQL, MySQL, SQL Server and Oracle all enforce declared types and lengths.