Data Types that SQLite Allows for Strict Tables

Posted on by Ian

SQLite is a lightweight, self-contained SQL database engine known for its simplicity and versatility. In version 3.37.0, SQLite introduced strict tables, offering stricter type enforcement compared to its regular tables.

This feature allows developers to define tables with precise data types, ensuring better data consistency.

In this article, we’ll look at the six supported data types for strict tables—INT, INTEGER, REAL, TEXT, BLOB, and ANY—and provide simple examples to illustrate their usage.

Overview of Strict Tables

SQLite traditionally uses a dynamic type system known as type affinity, where values can deviate from their declared column type. This is where you can insert a value that doesn’t adhere to the declared column type, and SQLite will accept it without a problem.

Strict tables, on the other hand, enforce type constraints rigorously. This ensures that the data matches the declared type, which enhances data integrity and predictability.

When defining a strict table, you must include the STRICT keyword in the table definition. For example:

CREATE TABLE example_table (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INT,
    data BLOB
) STRICT;

The STRICT keyword follows the end parenthesis of the table definition.

Strict tables allow the following explicit data types:

  • INT
  • INTEGER
  • REAL
  • TEXT
  • BLOB
  • ANY

Let’s explore each in detail.

INT

INT is used to store small or large whole numbers without fractional components. It is typically used for fields like counters, IDs, or attributes requiring integer values.

Characteristics

  • Represents whole numbers.
  • Storage depends on the size of the value (1, 2, 3, 4, 6, or 8 bytes).

Example

CREATE TABLE employees (
    id INT PRIMARY KEY,
    age INT NOT NULL,
    department_id INT
) STRICT;

INSERT INTO employees (id, age, department_id) VALUES (1, 25, 2);

Those values will be inserted, as they adhere to the defined column type of INT.

The following will raise an error:

INSERT INTO employees (id, age, department_id) VALUES (2, 'twenty-five', 2);

Output:

Runtime error: cannot store TEXT value in INT column employees.age (19)

Here, attempting to insert a string into an INT column fails due to strict type enforcement.

INTEGER

INTEGER is an alias for INT but is often used when declaring primary keys. SQLite automatically assigns the INTEGER column type for the ROWID (a unique identifier for each row). An INTEGER PRIMARY KEY column is an alias for the ROWID, but an INT PRIMARY KEY column is not

Characteristics

  • Functions identically to INT in strict tables.
  • Alias for ROWID when marked as PRIMARY KEY.

Example

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    stock INTEGER NOT NULL
) STRICT;

INSERT INTO products (stock) VALUES (50);

SELECT * FROM products;

Output:

product_id  stock
----------  -----
1           50

Here, we didn’t specify a value for the primary key column and so SQLite inserted 1.

The following will raise an error:

INSERT INTO products (stock) VALUES (50.5);

Output:

Runtime error: cannot store REAL value in INTEGER column products.stock (19)

In this case, SQLite enforces that stock must contain only whole numbers.

REAL

REAL represents floating-point numbers, suitable for storing values requiring precision, such as prices, measurements, or scientific data.

Characteristics

  • Uses an 8-byte IEEE 754 floating-point representation.
  • Supports fractional numbers.

Example

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    amount REAL NOT NULL
) STRICT;

INSERT INTO orders (order_id, amount) VALUES (1, 99.99);

The following will raise an error:

INSERT INTO orders (order_id, amount) VALUES (2, 'One MILLION');

Output:

Runtime error: cannot store TEXT value in REAL column orders.amount (19)

The REAL type enforces that values must be numeric, rejecting strings or non-numeric inputs.

Bear in mind that SQLite can sometimes convert a text value into an INTEGER or REAL value, depending on the value It can do this if it’s a well-formed integer or real literal.

Example:

INSERT INTO orders (order_id, amount) VALUES (2, '99.99');

In this case I surrounded 99.99 in single quotes, which would normally make it a text value. However, SQLite can work out that the number is a real literal and so it inserts a REAL value into the database.

TEXT

TEXT stores text strings of any length, commonly used for names, descriptions, or other character data.

Characteristics

  • Stores data as UTF-8, UTF-16BE, or UTF-16LE encoding.
  • Allows variable-length strings.

Example

CREATE TABLE customers (
    customer_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT UNIQUE
) STRICT;

INSERT INTO customers (customer_id, name, email) VALUES (1, 'Aneka', '[email protected]');

That will insert the text values as expected.

However, the following will also run without error:

INSERT INTO customers (customer_id, name, email) VALUES (2, 'Fitch', 12345);

In this example, I provided an integer for the email column, but SQLite was able to convert it to a text value.

BLOB

BLOB (Binary Large Object) is used for storing binary data such as images, files, or serialized objects.

Characteristics

  • Stores data exactly as it is without any conversion.
  • Ideal for non-textual data.

Example

CREATE TABLE files (
    file_id INT PRIMARY KEY,
    file_name TEXT NOT NULL,
    file_data BLOB NOT NULL
) STRICT;

INSERT INTO files (file_id, file_name, file_data) VALUES (1, 'image.png', x'FFD8FFE000104A464946');

The following will raise an error:

INSERT INTO files (file_id, file_name, file_data) VALUES (2, 'document.txt', 'binarydata');

Output:

Runtime error: cannot store TEXT value in BLOB column files.file_data (19)

The file_data column expects binary input (BLOB), rejecting any other type such as plain text.

ANY

ANY is a flexible type introduced for scenarios where the column can store values of any data type.

Characteristics

  • Accepts any data type (INT, INTEGER, REAL, TEXT, BLOB, or NULL).
  • Useful for generic or polymorphic data.
  • Provides us with flexibility even on strict tables.

Example

CREATE TABLE settings (
    setting_id INT PRIMARY KEY,
    name TEXT NOT NULL,
    value ANY
) STRICT;

INSERT INTO settings (setting_id, name, value) VALUES (1, 'max_users', 100);
INSERT INTO settings (setting_id, name, value) VALUES (2, 'theme', 'dark');
INSERT INTO settings (setting_id, name, value) VALUES (3, 'compression', x'1F8B0800');

The ANY type is versatile but requires careful handling to ensure logical consistency in usage.

It’s also important to note that strict tables handle ANY columns slightly differently to regular tables:

  • In regular tables, SQLite will attempt to convert strings that look like numbers into a numeric value, and if successful will store the numeric value rather than the original string.
  • In strict tables on the other hand, the value is stored exactly as it was provided. No conversion is performed, regardless of the value.

Benefits of Strict Tables

  • Improved Data Integrity: Strict tables enforce type constraints, reducing errors caused by inconsistent data.
  • Predictable Behavior: Data types behave uniformly, leading to fewer unexpected results.
  • Enhanced Debugging: Errors due to type mismatches are caught during data entry, making debugging easier.

Conclusion

SQLite’s strict table feature is a powerful enhancement for applications demanding robust data integrity and predictable behavior. By supporting well-defined data types—INT, INTEGER, REAL, TEXT, BLOB, and ANY—strict tables allow developers to build reliable databases while retaining SQLite’s lightweight and flexible nature.