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
INTEGERandNUMERIC: 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.
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.
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.
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 integer12. - 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
CHECKconstraints (CHECK (typeof(qty) = 'integer')) or useSTRICTwhen you need type safety. - Use
CASTandTYPEOF()when you debug unexpected comparisons. - Remember that PostgreSQL, MySQL, SQL Server and Oracle all enforce declared types and lengths.