SQLite STRICT Tables: Enforce Column Types (2026)

SQLite STRICT tables are a game-changer for data integrity, but the most surprising thing is how they still let you shoot yourself in the foot, just differently.

Let’s see STRICT tables in action. Imagine you’re building a simple inventory system.

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

CREATE TABLE inventory (
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    last_updated TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
);

Now, let’s make these STRICT:

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

CREATE TABLE inventory (
    product_id INTEGER NOT NULL,
    quantity INTEGER NOT NULL CHECK (quantity >= 0),
    last_updated TEXT DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id)
) STRICT;

The STRICT keyword at the end of the CREATE TABLE statement is the magic. What does it actually do?

Internally, SQLite uses a concept called "type affinity" for columns. Without STRICT, SQLite is very forgiving. If you declare a column as INTEGER but try to insert "hello", it might silently store 0 or NULL depending on the SQLite version and context. This is convenient for some use cases, but it’s a breeding ground for subtle bugs.

STRICT tables change this behavior dramatically. When you define a column with a specific type affinity (like INTEGER, TEXT, REAL, BLOB, NUMERIC), STRICT enforces that the data you insert must conform to that affinity. If you try to insert text into an INTEGER column, or a number into a TEXT column where it doesn’t make sense, you’ll get an error.

Let’s try inserting data into our STRICT products table:

INSERT INTO products (id, name, price) VALUES (1, 'Laptop', 1200.50); -- Works
INSERT INTO products (id, name, price) VALUES (2, 'Keyboard', 75); -- Works (75 is compatible with REAL)
INSERT INTO products (id, name, price) VALUES (3, 'Mouse', 'cheap'); -- ERROR: type mismatch

The third insert fails because 'cheap' cannot be interpreted as a REAL number. This is exactly what STRICT is for: preventing these kinds of type mismatches at the point of insertion, rather than discovering them later when your calculations are producing garbage.

The NOT NULL and CHECK constraints are also enforced more rigorously. If you have quantity INTEGER NOT NULL and try INSERT INTO inventory (product_id, quantity) VALUES (1, NULL), it will fail. The CHECK (quantity >= 0) constraint ensures that you can’t insert negative quantities.

Now, let’s consider the levers you control with STRICT tables:

• Type Enforcement: This is the core. You declare INTEGER, TEXT, REAL, BLOB, or NUMERIC. STRICT makes sure values inserted conform to the declared affinity. NUMERIC is a bit of a wildcard, accepting integers, floats, and strings that can be interpreted as numbers.
• Column Names: STRICT tables also prevent you from referencing columns that don’t exist, which is a common typo-related bug. INSERT INTO products (id, name, cost) VALUES (4, 'Monitor', 300); would fail because there’s no cost column.
• Constraints: NOT NULL, UNIQUE, CHECK, and FOREIGN KEY constraints are all validated.

While STRICT tables are fantastic for data integrity, they don’t eliminate all potential issues. For instance, if you declare a column as TEXT and insert a valid number string like '123', it will be stored as text. If you later try to perform arithmetic on this TEXT column without explicitly casting it (e.g., SELECT quantity * 2 FROM inventory WHERE product_id = 1), SQLite might perform text concatenation ('50' || '2' becomes '502') instead of numeric multiplication, leading to unexpected results. The STRICT keyword enforces the storage type, not necessarily the implicit conversion during operations unless the operation itself is type-sensitive and the target type affinity doesn’t match.

The next logical step after enforcing data types is to ensure the relationships between your data are sound, which often leads to exploring advanced foreign key behaviors and cascading actions.