An Introduction to Strict Tables in SQLite

SQLite is widely known for its simplicity, flexibility, and lightweight architecture. One feature that sets it apart from most other SQL databases is its dynamic typing system, which allows columns in a table to store data of any type, regardless of their declared type.

While some developers welcome this departure from the traditional SQL approach, others find it extremely problematic, due to its non-enforcement of data types, which could potentially lead to data integrity issues.

To address this, SQLite introduced strict tables in version 3.37.0 (released in November 2021). Strict tables enforce stricter type rules, ensuring that the data stored in a table matches the column’s declared type.

This article explores the concept of strict tables in SQLite, explains their benefits, and provides an example of their practical use.

What Are Strict Tables?

Strict tables are a special table mode in SQLite that enforces rigid typing and additional constraints on data. In contrast to traditional SQLite tables, which use type affinity to store values, strict tables demand that every value inserted into a column matches its declared type exactly. This added level of enforcement helps prevent unintentional data anomalies and promotes consistency in database design.

Strict tables are created using the STRICT table option when defining a table schema. This mode applies the following rules:

1. Exact Type Matching: Values must match the declared column type exactly, and any type mismatch results in an error.
2. No Implicit Type Conversions: Automatic type coercion (e.g., storing a string in an integer column) is disallowed.

Why Use Strict Tables?

Strict tables are particularly beneficial in scenarios where data integrity and predictability are paramount. Here are some key advantages:

• Improved Data Integrity: By ensuring that data matches the declared types, strict tables help maintain consistent and reliable datasets.
• Error Prevention: Preventing implicit type conversions reduces the risk of bugs and unexpected behaviors in applications.
• Alignment with Static Typing: For developers accustomed to strongly-typed languages, strict tables provide a familiar and predictable behavior.
• Better Compatibility with Tools: Some tools and frameworks expect strict typing. Using strict tables ensures smoother integration.

How to Create a Strict Table

To create a strict table, use the STRICT keyword at the end of the CREATE TABLE statement. Below is an example of creating and working with a strict table.

Example: Defining and Using a Strict Table

Consider a database for managing employee records. Each record must include an employee ID (integer), name (text), hire date (date), and salary (real).

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    hire_date TEXT NOT NULL,
    salary REAL NOT NULL
) STRICT;

Explanation of the schema:

• id INTEGER PRIMARY KEY: The id column serves as the unique identifier for each employee. The INTEGER PRIMARY KEY constraint ensures that values are unique and sequential.
• name TEXT NOT NULL: The name column stores the employee’s name. The TEXT type ensures that only string data can be stored here.
• hire_date TEXT NOT NULL: The hire_date column stores the date the employee was hired. SQLite doesn’t have a dedicated DATE type, and so we need to use TEXT.
• salary REAL NOT NULL: The salary column stores numeric values, enforcing a REAL type.
• STRICT: This is the part that defines the table as a strict table. The STRICT keyword goes after the closing parenthesis.

Attempting to Insert Data

Valid data will be inserted without issue:

INSERT INTO employees (id, name, hire_date, salary) 
VALUES (1, 'Amber', '2024-01-15', 75000.00);

Invalid data will result in an error. For instance, attempting to insert a string into the salary column will fail:

INSERT INTO employees (id, name, hire_date, salary) 
VALUES (2, 'Sia', '2024-02-01', 'seventy-five thousand');

Output:

Runtime error: cannot store TEXT value in REAL column employees.salary (19)

However, inserting a value with the wrong format for hire_date will not necessarily fail. For example we could do this:

INSERT INTO employees (id, name, hire_date, salary) 
VALUES (3, 'Nate', '15-01-2024', 50000.00);

We could even do this:

INSERT INTO employees (id, name, hire_date, salary) 
VALUES (4, 'Brandy', 'I like bananas!', 150000.00);

This is because we defined the column as TEXT, due to the fact that SQLite doesn’t have a DATE type.

Querying the Data

Strict tables behave the same as regular tables during querying:

SELECT * FROM employees;

Result:

id  name    hire_date        salary  
--  ------  ---------------  --------
1   Amber   2024-01-15       75000.0 
3   Nate    15-01-2024       50000.0 
4   Brandy  I like bananas!  150000.0

Differences Between Strict Tables and Regular Tables

The following table outlines the difference between strict and regular tables in a nutshell:

Feature	Regular Tables	Strict Tables
Type Enforcement	Uses type affinity (flexible)	Enforces exact type matching
Implicit Type Conversions	Allowed	Disallowed
Error Handling on Type Mismatch	Converts data or truncates it	Raises an error
Use Case	General-purpose storage	High-integrity data requirements

Allowed Data Types

At the time of writing, the following data types are allowed:

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

Specifying any other data type results in an error when the STRICT keyword is used. Removing the STRICT keyword allows the table to be created (but not as a strict table).

More Information

For more information, see SQLite’s documentation on strict tables.