Determination Of Affinity
The following rules within the order shown used to declared the kind of the column and the affinity of any column:
- If the declared column type has the string “INT” then it’s allotted integer affinity.
- If the declared column type has any of the strings like “TEXT”, “VARCHAR” or “CLOB” then that column has TEXT affinity.
- If the declared column type has the string “BLOB” or if no kind is given then the column has BLOB affinity.
- If the declared column type has strings like “FLOA” or “DOUB” then the column has REAL affinity.
- Otherwise, the affinity is NUMERIC.
Below table shows common datatype from SQL are converted into affinities by the 5 rules of the Determination Of Affinity for a small set of the datatype that SQLite can accept:
| SQLite data type | Type affinity |
|---|---|
| INT INTEGER TINYINT SMALLINT MEDIUMINT BIGINT UNSIGNED BIG INT INT2 INT8 | INTEGER |
| NUMERIC DECIMAL(10, 5) BOOLEAN DATE DATETIME | NUMERIC |
| REAL DOUBLE DOUBLE PRECISION FLOAT | REAL |
| CHARACTER(20) VARCHAR(255) VARYING CHARACTER(255) NCHAR(55) NATIVE CHARACTER(70) NVARCHAR(100) TEXT CLOB | TEXT |
| BLOB no datatype specified | NONE |
SQLite provides the typeof() function that could be used to check the storage class of a value based on its format.
SQLite Data Types
In SQLite, data types like INTEGER, TEXT, and REAL define the kind of data stored in a column, while storage classes (NULL, INTEGER, REAL, TEXT, BLOB) dictate how data is stored on disk. SQLite employs dynamic typing, allowing values to be stored without strict adherence to column types, but data type affinity still guides conversion and comparison operations. Understanding these concepts is essential for efficient SQLite database design and querying.
5 Primary Data Types in SQLite
- NULL- It is a NULL value.
- INTEGER- It is an integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the value.
- REAL- It is a floating point value, stored as an 8-byte floating number.
- TEXT- It is a string, stored using the database encoding (UTF).
- BLOB- It is a group of data, stored exactly as it was entered.