The SQLite strict Guide

SQLite is permissive by default

SQLite is famous for working with the absolute minimum of data specification. While this may help with prototyping it is really bad at catching mistakes early because a lot of unintended things are permitted.

A table definition can be as simple as:

create table notes(id, note);

This creates a table notes with the columns id and note. What is permitted inside these columns? Everything!

• Setting note and id both to NULL? Yes
• Having ten notes with the same id? Yes
• Mixing numbers, text and BLOB-encoded UUIDs in id? Completely fine
• Setting note to 3.1415 (the number)? Why not

As you can see this simple example allows a huge number of unintended variations of the data, most of these will probably make your program crash if actually encountered and it only gets worse with more complex schemata.

Who is this Guide for?

This guide is for everyone who wants their database to loudly complain about incorrect data the moment something tries to write it to catch errors early and save headaches later.

If you want to follow along you can do so by running sqlite3 and copying the examples into the resulting SQLite shell.

PRIMARY KEYs

Declare your primary keys as INTEGER PRIMARY KEY NOT NULL, this will make them a super fast alias for the rowid and autofill when you're inserting new columns.

Note: Using INT PRIMARY KEY will not work!

SQLite CREATE TABLE documentation

Explicit NULL and NOT NULL

By default SQLite assumes that any column is nullable and usually you don't want that because few things can ruin your day like a NULL in the wrong place.

Always declare NULL or NOT NULL with every column.

This way it is always explicit what the column expects.

STRICT Tables

SQLite by default does not do any actual type checking on the columns (except for some special cases). Putting STRICT as the last word of a CREATE TABLE statement changes that and makes SQLite check.

CREATE TABLE strict_example (
    id INTEGER PRIMARY KEY NOT NULL,
    foo TEXT NOT NULL,
    bar INTEGER NULL
) STRICT;

-- Will work
INSERT INTO strict_example(foo, bar) VALUES ('Human rights', 1);
INSERT INTO strict_example(foo, bar) VALUES ('Matter', NULL);

-- Automatic type conversion still happens
INSERT INTO strict_example(foo, bar) VALUES (123, '123');

-- Will not work
INSERT INTO strict_example(foo, bar) VALUES ('Matter', 'Antimatter');
-- Output:
-- Runtime error: cannot store TEXT value in INTEGER column strict_example.bar (19)

In strict tables you can use the following datatypes:

Constraints will be checked when modifying table records and can be explicitly checked using PRAGMA integrity_check and PRAGMA quick_check.

SQLite strict table documentation

CHECK() Constraints

CHECK(…) constraints can be used for additional validation of data in the same place as UNIQUE, between the parenthesis there must be an SQL expression that must evaluate to a nonzero value for the check to pass. The check is ran and must pass when rows are added or updated.

CREATE TABLE check_example (
    id INTEGER PRIMARY KEY NOT NULL,
    start_time INTEGER NOT NULL,
    end_time INTEGER NULL,
    CHECK(start_time <= end_time)
) STRICT;

-- Will work
INSERT INTO check_example(start_time, end_time) VALUES (1, 5);
INSERT INTO check_example(start_time, end_time) VALUES (1, NULL);

-- Will fail because of the check
INSERT INTO check_example(start_time, end_time) VALUES (2, 1);
-- Output:
-- Runtime error: CHECK constraint failed: start_time <= end_time (19)

Trying to construct a record where the start time is after the end time is not possible on this table. However leaving the end_time NULL will be possible because then start_time <= end_time evaluates to NULL.

Using subqueries inside the CHECK is not possible.

SQLite CHECK constraint documentation

UNIQUE INDEX

SQLite supports the UNIQUE keyword to make sure the values or combinations of values only occur once, this is a well known SQL feature, another way to enforce the same constraints is using CREATE UNIQUE INDEX.

The UNIQUE INDEX has the additional benefit of not only allowing one to use the columns of a table, but also SQL expressions (SQLite calls this an indexed-column) which can be be used to get creative with SQL constraints.

CREATE TABLE unique_example (
    id INTEGER PRIMARY KEY NOT NULL,
    txt TEXT NOT NULL
) STRICT;

CREATE UNIQUE INDEX case_insensititve
ON unique_example(lower(txt));

-- Will work
INSERT INTO unique_example(txt) VALUES ('Abc');

-- Will fail becuase a diffent abc is already present
INSERT INTO unique_example(txt) VALUES ('ABC');
-- Output:
-- Runtime error: UNIQUE constraint failed: index 'case_insensititve' (19)

Note on the Example: This specific constraint could also be applied with a COLLATE NOCASE in combination with a regular UNIQUE on the column.

SQLite unique index documentation

Combining UNIQUE and NULL

SQLite handles combining NULL and UNIQUE by allowing NULL to appear multiple times within a unique column.

This behaviour may be different from a database you are used to as some other databases only allow one NULL per unique column.

SQLite NULL handling documentation and comparison

Foreign Keys and REFERENCES

Enable foreign key support with:

PRAGMA foreign_keys = ON;

Add a REFERENCES {parent_table}({parent_column}) where {parent_table} and {parent_column} identify the database table and column the ids in the annotated column point to. The REFERENCES must point at columns that are a PRIMARY KEY or have a UNIQUE constraint.

In SQLite the terminology is always that a child table references a parent table.

It also protects the references from breaking by prohibiting any changes to the fields in the parent table that are referenced by any child tables. See the next section on how to configure this.

PRAGMA foreign_keys = ON;

CREATE TABLE example_category (
    c_id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

INSERT INTO example_category(c_id, name) VALUES
    (1, 'foo'),
    (2, 'bar'),
    (3, 'baz');

CREATE TABLE example_item (
    i_id INTEGER NOT NULL PRIMARY KEY,
    category INTEGER NULL REFERENCES example_category(c_id)
) STRICT;

-- Will work
INSERT INTO example_item(category) VALUES (1);
INSERT INTO example_item(category) VALUES (NULL);

-- Will fail because there is no category with id 30
INSERT INTO example_item(category) VALUES (30);
-- Output:
-- Runtime error: FOREIGN KEY constraint failed (19)

SQLite foreign key documentation

ON UPDATE and ON DELETE

This is an extension of the REFERENCES clause in the previous section. The REFERENCES itself can make sure that no new columns violate the schema while ON UPDATE and ON DELETE make sure, they stay valid when UPDATE and DELETE are used on the referenced ("parent") rows by deciding how those operations should be handled.

NO ACTION

No action is taken when the parent key is modified, this is the same behaviour as without any REFERENCES and allows breaking the reference without any intervention or warning from the database.

RESTRICT

This will cause any operation that would result in changed or broken references to throw the error "FOREIGN KEY constraint failed (19)". This is the default when a REFERENCES doesn't specify any ON UPDATE or ON DELETE.

SET NULL

This will set the reference field to NULL on the operation(s) it is configured for.

SET DEFAULT

This will set the reference field to its DEFAULT value on the operation(s) it is configured for.

CASCADE

This will make the references follow what the parent is doing, on an update all referencing fields will be updated too, on a deletion all referencing fields will be deleted too.

PRAGMA foreign_keys = ON;

CREATE TABLE example_run (
    r_id INTEGER NOT NULL PRIMARY KEY,
    name TEXT NOT NULL
) STRICT;

INSERT INTO example_run(r_id, name) VALUES
    (1, 'blubber'),
    (2, 'dragon');

CREATE TABLE example_log (
    l_id INTEGER NOT NULL PRIMARY KEY,
    run INTEGER NULL REFERENCES example_run(r_id) ON UPDATE CASCADE ON DELETE CASCADE,
    message TEXT NOT NULL
) STRICT;

INSERT INTO example_log(run, message) VALUES
	(1, 'blah'),
	(1, 'blubb'),
	(1, 'done'),
	(2, 'dragon goes "miep"'),
	(2, 'dragon receives hug');

-- Move ids of the run around for demonstration purposes
UPDATE example_run SET r_id = 42 WHERE r_id = 2;

-- All entries previously associated with run 2 have now been rewritten to run 42
SELECT run, message FROM example_log;
-- Output:
-- 1|blah
-- 1|blubb
-- 1|done
-- 42|dragon goes "miep"
-- 42|dragon receives hug

-- Will delete the run
DELETE FROM example_run WHERE r_id = 1;

-- And also the log entries associated with the run:
-- This will return no entries where the run is set to 1.
SELECT run, message FROM example_log;
-- Output:
-- 42|dragon goes "miep"
-- 42|dragon receives hug

SQLite ON DELETE and ON UPDATE documentation

See also: Triggers

Triggers can be a way to enforce other constraints on the data model, even spanning multiple tables and producing custom error messages using the RAISE() function.

However, getting triggers right would a it's own guide and usually one wants INSERT and UPDATE triggers leading to a lot of duplicate code that is difficult to debug.

In a nutshell: Triggers are powerful but difficult, use them only when there is absolutely no other option.

SQLite CREATE TRIGGER documentation