SQLite Strict Tables: Why Schema Enforcement Matters

beginner recent 8 min read updated 15 Aug 2026

On this page 5

SQLite Type System: Flexibility vs. Strictness
Declaring STRICT Tables: Syntax and Setup
Strict Mode Operations: Insert, Update, Query
Strict Table Pitfalls: Common Type Mismatches
Building a Strict Schema: Hands-on Exercise

SQLite Type System: Flexibility vs. Strictness

SQLite’s default type system does not strictly enforce declared column types. Instead, it employs a concept called “type affinity,” which suggests a preferred data type for a column but allows values of other types to be stored. This flexibility, while sometimes convenient, can lead to significant data integrity issues and unexpected query behavior.

Consider a simple users table with an age column declared as INTEGER.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    age INTEGER
);

By default, SQLite permits inserting values that do not match the declared INTEGER type. For example, a string or a floating-point number can be stored in the age column.

INSERT INTO users (name, age) VALUES ('Alice', 30);
INSERT INTO users (name, age) VALUES ('Bob', 'forty');
INSERT INTO users (name, age) VALUES ('Charlie', 25.5);

Querying the stored values and their internal types reveals how SQLite handles these insertions:

SELECT name, age, typeof(age) FROM users;

name     age     typeof(age)
-------  ------  -----------
Alice    30      integer
Bob    forty   text
Charlie  25      integer

The age column, despite its INTEGER affinity, contains both integer and text data types. SQLite’s INTEGER affinity rule attempts to convert 25.5 to 25 (an integer) but stores 'forty' as text because it cannot be converted to a number. This demonstrates that the declared type is a suggestion for storage preference, not a strict validation rule.

This behavior undermines data integrity. An application expecting numeric values in the age column might receive a string, causing runtime errors or requiring additional validation logic. Furthermore, database operations can produce misleading results. For instance, an aggregate function like SUM(age) would silently ignore the 'forty' entry, potentially skewing the total or average age without any explicit error or warning. This makes debugging more complex, as data inconsistencies might only surface during specific computations or application paths.

The tradeoff for this flexibility is a reduced guarantee of data consistency within the database itself. Without strict enforcement, the responsibility for type validation shifts entirely to the application layer, increasing development effort and the risk of data corruption over time.

Declaring STRICT Tables: Syntax and Setup

To enable strict type enforcement, append the STRICT keyword at the end of a standard CREATE TABLE statement. This modifier instructs SQLite to reject values that do not conform to the declared column type, rather than attempting a type conversion or storing the data using a different affinity.

Consider a table designed to store product information. Without STRICT, SQLite’s default type affinity rules might allow a string like '123' to be stored in an INTEGER column. With STRICT, such an insertion attempt will fail, ensuring data consistency at the database level.

Here is the basic syntax for creating a strict table:

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL,
    stock_count INTEGER DEFAULT 0,
    description TEXT
) STRICT;

In this products table, product_id and stock_count are explicitly INTEGER, name and description are TEXT, and price is REAL. When you attempt to insert data into a STRICT table, SQLite validates each value against its declared column type.

For instance, inserting valid data proceeds as expected:

INSERT INTO products (product_id, name, price, stock_count)
VALUES (101, 'Widget A', 29.99, 150);

However, attempting to insert a string into an INTEGER or REAL column will result in an error. The database prevents the operation, maintaining the defined schema integrity.

INSERT INTO products (product_id, name, price, stock_count)
VALUES (102, 'Gadget B', 'forty-five', 75);

Output from the above INSERT statement:

Error: datatype mismatch

SQLite’s STRICT tables enforce types for INTEGER, REAL, TEXT, and BLOB columns. Any attempt to store data of a different fundamental type into these columns will be rejected. Columns declared with other types, such as BOOLEAN or DATETIME, still map to one of these core types (e.g., INTEGER for BOOLEAN, TEXT or INTEGER for DATETIME) and strictness applies based on that underlying affinity.

A NOT NULL constraint still functions independently. A STRICT table will reject NULL values for NOT NULL columns, in addition to rejecting type mismatches. Similarly, DEFAULT values are applied if no value is provided, and these default values must also conform to the column’s declared type.

Strict Mode Operations: Insert, Update, Query

Strict tables enforce type affinity checks during data manipulation operations. This means SQLite will reject data that does not match the declared column type, preventing silent type coercion or storage of malformed values. This behavior applies directly to INSERT and UPDATE statements.

Consider a products table defined with STRICT mode:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    stock INTEGER DEFAULT 0
) STRICT;

When inserting data, values must align with their column’s declared type. Inserting a string into an INTEGER or REAL column, for example, will fail.

INSERT INTO products (id, name, price, stock) VALUES (1, 'Laptop', 1200.50, 50);
-- This succeeds.

Attempting to insert a text literal into the price column, which is REAL, triggers an error:

INSERT INTO products (id, name, price, stock) VALUES (2, 'Monitor', '999.99', 20);

This operation produces the following output:

Error: datatype mismatch

Similarly, UPDATE operations also respect strict type checking. Changing a column’s value to an incompatible type will trigger an error.

UPDATE products SET stock = 45 WHERE id = 1;
-- This succeeds.

An UPDATE statement attempting to set price to a string also results in a datatype mismatch error:

UPDATE products SET price = 'invalid_price' WHERE id = 1;

This immediate feedback prevents corruption of the database’s type integrity.

Querying data with SELECT statements does not inherently change behavior in strict mode. SELECT operations retrieve data as stored. The key difference is the guarantee that the retrieved data will always conform to the schema’s declared types because strict mode prevented any non-conforming data from being written in the first place. This eliminates the need for application-level type validation on read, simplifying client code and improving reliability. This enforcement ensures that id will always be an integer, name always text, and price always a real number, without relying on SQLite’s traditional flexible typing.

Strict Table Pitfalls: Common Type Mismatches

Strict tables enforce type affinity at insertion time, a direct contrast to SQLite’s default flexible typing. This means data must conform to the declared column type, or an SQLITE_CONSTRAINT_DATATYPE error will occur. Understanding these type mismatches is key to using strict tables effectively.

Consider a table designed to store user ages as integers:

CREATE TABLE users_strict (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    age INTEGER NOT NULL
) STRICT;

Attempting to insert a non-integer string into the age column will fail:

INSERT INTO users_strict (id, name, age) VALUES (1, 'Alice', 'twenty');
-- Error: SQLITE_CONSTRAINT_DATATYPE: datatype mismatch

SQLite does not implicitly convert the string 'twenty' to an integer 20. The INTEGER column expects a numeric integer value, not arbitrary text.

Similarly, an INTEGER column will reject floating-point numbers, even if they appear to be whole numbers.

INSERT INTO users_strict (id, name, age) VALUES (2, 'Bob', 25.0);
-- Error: SQLITE_CONSTRAINT_DATATYPE: datatype mismatch

While 25.0 is numerically equivalent to 25, its underlying type is REAL (floating-point). A strict INTEGER column expects an integer literal or a value that is unambiguously an integer type. For such cases, cast the value explicitly: CAST(25.0 AS INTEGER) or simply use 25.

SQLite lacks a native BOOLEAN type. The standard practice is to represent boolean values as INTEGER (0 for false, 1 for true). If a column is declared BOOLEAN, SQLite treats it as an INTEGER column. Inserting string literals like 'TRUE' or 'FALSE' will result in a type mismatch.

CREATE TABLE settings_strict (
    key TEXT PRIMARY KEY,
    enabled BOOLEAN NOT NULL
) STRICT;

INSERT INTO settings_strict (key, enabled) VALUES ('feature_x', 'TRUE');
-- Error: SQLITE_CONSTRAINT_DATATYPE: datatype mismatch

To correctly store boolean values in an INTEGER column, use 0 or 1:

INSERT INTO settings_strict (key, enabled) VALUES ('feature_x', 1);
-- Success

To avoid these pitfalls, ensure your application code sends data types that precisely match the column definitions. Use prepared statements with parameter binding where possible. Client libraries often handle the serialization of native types (e.g., Python int to SQLite INTEGER) correctly, preventing many common mismatches before the SQL statement reaches the database engine.

Building a Strict Schema: Hands-on Exercise

To illustrate strict table behavior, we will set up a small product inventory database. This schema will manage basic product information, including name, price, and stock quantity. The goal is to prevent common data entry errors by enforcing specific data types for each column.

Consider a products table. We define product_id as an INTEGER PRIMARY KEY, name as TEXT NOT NULL, price as REAL NOT NULL, and quantity as INTEGER NOT NULL with a default of 0. The last_updated column will store a timestamp as TEXT. The STRICT keyword appended to the CREATE TABLE statement activates the type enforcement.

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    price REAL NOT NULL,
    quantity INTEGER NOT NULL DEFAULT 0,
    last_updated TEXT NOT NULL DEFAULT (strftime('%Y-%m-%d %H:%M:%S', 'now'))
) STRICT;

Now, insert some valid data. These statements adhere to the defined column types and constraints.

INSERT INTO products (name, price, quantity) VALUES ('Laptop', 1200.50, 15);
INSERT INTO products (name, price, quantity) VALUES ('Mouse', 25.00, 100);

Attempting to insert data that violates the schema’s type constraints will result in an error. For example, providing a text string for the price column, which expects a REAL value, will fail. SQLite will not attempt implicit type conversions in a strict table.

INSERT INTO products (name, price, quantity) VALUES ('Keyboard', 'fifty', 50);

This insertion attempt produces an error like Error: in prepare, table products has strict affinity, cannot store TEXT in column price. Similarly, trying to store a floating-point number in an INTEGER column, such as quantity, will also fail.

INSERT INTO products (name, price, quantity) VALUES ('Monitor', 300.00, 10.5);

This command yields Error: in prepare, table products has strict affinity, cannot store REAL in column quantity. Strict tables prevent these common data integrity issues at the point of insertion or update. This enforcement ensures that the database consistently holds the expected data types, reducing application-level validation complexity and potential runtime errors.

Spotted an error? Tell us via the corrections process — verified reports get fixed and credited.