SQLite Basics: Protect Rows with NOT NULL, CHECK, and UNIQUE

Constraints put basic validation next to the data. These isolated examples use INSERT OR IGNORE to demonstrate rejected rows without stopping the teaching runner. Real applications should normally report invalid input instead of silently skipping it.

Prepare a scratch directory

Tested on Linux with Python 3.12.14 and SQLite 3.53.1. You need Bash, Python 3 with SQLite support, a text editor, and mktemp, rm, and rmdir. No additional Python packages are needed. Keep the same terminal open and save files as UTF-8.

start_dir="$PWD"
work_dir="$(mktemp -d)" || exit 1
cd -- "$work_dir" || exit 1

The standalone SQLite CLI was unavailable on the test machine. Save this runner as run-sql.py. Each invocation executes SQL in a fresh in-memory database and prints pipe-separated rows, displaying missing values as NULL. These toy databases disappear when the connection closes. Put each statement’s ending semicolon at the end of a line, as shown; this is a teaching runner, not a general SQL shell or CSV exporter.

from pathlib import Path
import sqlite3
import sys

connection = sqlite3.connect(":memory:", isolation_level=None)
try:
    statement = ""
    for line in Path(sys.argv[1]).read_text(encoding="utf-8").splitlines(True):
        statement += line
        if sqlite3.complete_statement(statement):
            cursor = connection.execute(statement)
            for row in cursor:
                print("|".join("NULL" if value is None else str(value) for value in row))
            statement = ""
    if statement.strip():
        raise ValueError("Unfinished SQL statement")
finally:
    connection.close()

1. Require a value

Save as required-name.sql:

CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO members VALUES (1, 'Ada');
INSERT OR IGNORE INTO members VALUES (2, NULL);
SELECT changes() AS inserted;
SELECT id, name FROM members ORDER BY id;

Run:

python3 run-sql.py required-name.sql

Output:

0
1|Ada

The zero means the attempted insert changed no rows. NOT NULL excludes NULL, but allows empty strings. Without OR IGNORE, this invalid insert raises a constraint error.

2. Keep quantities nonnegative

Save as checked-quantity.sql:

CREATE TABLE stock (
    sku TEXT NOT NULL UNIQUE,
    quantity INTEGER NOT NULL CHECK (quantity >= 0)
);
INSERT INTO stock VALUES ('pen', 4);
INSERT OR IGNORE INTO stock VALUES ('book', -1);
SELECT changes() AS inserted;
SELECT sku, quantity FROM stock ORDER BY sku;

Run:

python3 run-sql.py checked-quantity.sql

Output:

0
pen|4

A CHECK expression that evaluates to zero rejects the row. NULL alone does not fail a CHECK, so combine it with NOT NULL when a value is required. Ordinary SQLite type affinity is not strict type enforcement.

3. Prevent duplicate business keys

Save as unique-code.sql:

CREATE TABLE products (code TEXT NOT NULL UNIQUE, label TEXT NOT NULL);
INSERT INTO products VALUES ('P1', 'Pen');
INSERT OR IGNORE INTO products VALUES ('P1', 'Replacement label');
SELECT changes() AS inserted;
INSERT INTO products VALUES ('P2', 'Book');
SELECT code, label FROM products ORDER BY code;

Run:

python3 run-sql.py unique-code.sql

Output:

0
P1|Pen
P2|Book

The duplicate key leaves the existing label unchanged. A UNIQUE column can contain multiple NULL values; NOT NULL closes that gap for required codes. OR IGNORE is a conflict policy, not a substitute for checking outcomes.

SQLite’s CREATE TABLE reference documents these constraints; the conflict resolution reference explains IGNORE.

Clean up

In the same terminal, remove only these example files and the empty scratch directory:

rm -- run-sql.py required-name.sql checked-quantity.sql unique-code.sql
cd -- "$start_dir" || exit 1
rmdir -- "$work_dir"
unset start_dir work_dir

Acknowledgements

This post was created with assistance from AI.