SQLite NOT NULL and DEFAULT: Column Constraints That Stick

Two Constraints That Earn Their Keep

Most of the bugs that come from a sloppy schema trace back to one of two things: a column that's NULL when nobody expected it to be, or a column that's missing a value the application assumed was there. NOT NULL and DEFAULT solve both — and they cost almost nothing to add.

CREATE TABLE users (
    id       INTEGER PRIMARY KEY,
    email    TEXT    NOT NULL,
    name     TEXT    NOT NULL DEFAULT 'anonymous',
    joined   TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP)
);

INSERT INTO users (email) VALUES ('rosa@example.com');

SELECT * FROM users;

One column is required and has no fallback. Two have fallbacks. The insert only had to supply email, and SQLite filled in the rest. That's the whole feature in one example — the rest of this page is about the edges.

NOT NULL Means "Reject NULL, No Exceptions"

NOT NULL does exactly what it says. Any attempt to put NULL into the column — whether by omitting it from an INSERT with no default, or by writing NULL explicitly — fails:

CREATE TABLE posts (
    id    INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

-- This fails: title is missing.
INSERT INTO posts (id) VALUES (1);

The error reads:

Runtime error: NOT NULL constraint failed: posts.title

Same outcome if you pass NULL directly:

INSERT INTO posts (id, title) VALUES (1, NULL);
-- Runtime error: NOT NULL constraint failed: posts.title

That's the contract. If a column is logically required, mark it NOT NULL and you've taken a whole class of bugs off the table — no application code can sneak a NULL past the database.

DEFAULT Provides a Value When the Caller Doesn't

DEFAULT kicks in only when an INSERT doesn't mention the column at all. It does not rescue an explicit NULL:

CREATE TABLE tasks (
    id     INTEGER PRIMARY KEY,
    title  TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending'
);

INSERT INTO tasks (title) VALUES ('Write docs');
INSERT INTO tasks (title, status) VALUES ('Ship it', 'done');

SELECT * FROM tasks;

The first insert relies on the default. The second overrides it. If you'd written INSERT INTO tasks (title, status) VALUES ('x', NULL), you'd get a NOT NULL constraint failed error — the column was named, so the default never fires.

This is the mental model worth holding: DEFAULT fills in for missing columns. NOT NULL rejects nulls however they arrive. They're independent features that compose well.

Defaults Can Be Expressions

A literal default is the common case (DEFAULT 0, DEFAULT '', DEFAULT 'pending'), but SQLite also accepts an expression in parentheses. This is how you stamp rows with the time they were created, or generate a random ID:

CREATE TABLE events (
    id         INTEGER PRIMARY KEY,
    name       TEXT NOT NULL,
    occurred   TEXT NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    token      TEXT NOT NULL DEFAULT (lower(hex(randomblob(8))))
);

INSERT INTO events (name) VALUES ('login');
INSERT INTO events (name) VALUES ('logout');

SELECT * FROM events;

A few things to know:

• The expression is evaluated on every insert, not once at table-creation time. Each row gets its own timestamp and its own token.
• CURRENT_TIMESTAMP, CURRENT_DATE, and CURRENT_TIME are the three special keywords that don't need parentheses. Anything else does.
• The expression can't reference other columns or subqueries — it has to be self-contained.

If you want a column to be optional but auto-stamped when present, drop the NOT NULL and keep the default. If you want it required and auto-stamped, use both.

DEFAULT NULL Is Legal (and Sometimes the Point)

Writing DEFAULT NULL is the same as having no default at all — the column is NULL when you don't supply a value. It's worth using when you want to be explicit in the schema that "no value" is the intended starting state:

CREATE TABLE profiles (
    id        INTEGER PRIMARY KEY,
    user_id   INTEGER NOT NULL,
    bio       TEXT DEFAULT NULL,
    avatar    TEXT
);

INSERT INTO profiles (user_id) VALUES (1);

SELECT * FROM profiles;

bio and avatar behave identically here. The DEFAULT NULL on bio is a comment in the form of code — it tells anyone reading the schema that the absence of a bio is a normal state, not an oversight.

Adding NOT NULL to an Existing Table

This is where things get fiddly. SQLite's ALTER TABLE is intentionally limited — you can't run ALTER COLUMN ... SET NOT NULL like you might in Postgres. What you can do depends on whether the column already exists.

For a brand-new column, ADD COLUMN ... NOT NULL works, but you must supply a default — otherwise existing rows would suddenly contain NULL in a NOT NULL column, which is impossible:

CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
INSERT INTO products (name) VALUES ('widget'), ('gizmo');

-- Works: existing rows get the default.
ALTER TABLE products ADD COLUMN price REAL NOT NULL DEFAULT 0;

SELECT * FROM products;

Try the same without the default and you'll get an error:

ALTER TABLE products ADD COLUMN sku TEXT NOT NULL;
-- Runtime error: Cannot add a NOT NULL column with default value NULL

For an existing column, there's no in-place change. The standard recipe is the rebuild dance: create a new table with the constraint you want, copy data across, drop the old one, rename the new. We'll cover that in the drop-and-alter-table page — for now, just know the limitation is real and plan your schema with it in mind.

A Realistic Combination

Most production tables use both constraints together to encode "what the application expects to be true":

CREATE TABLE orders (
    id          INTEGER PRIMARY KEY,
    customer    TEXT    NOT NULL,
    total_cents INTEGER NOT NULL DEFAULT 0,
    currency    TEXT    NOT NULL DEFAULT 'USD',
    status      TEXT    NOT NULL DEFAULT 'pending',
    created_at  TEXT    NOT NULL DEFAULT (CURRENT_TIMESTAMP),
    notes       TEXT
);

INSERT INTO orders (customer) VALUES ('Ada');
INSERT INTO orders (customer, total_cents, currency) VALUES ('Boris', 4999, 'EUR');

SELECT id, customer, total_cents, currency, status, created_at FROM orders;

Read that schema top to bottom and you can guess what the application does without seeing a single line of code. customer is required and has no fallback — the caller has to know who the order is for. Money, currency, and status all have sensible defaults, so the simplest insert still produces a coherent row. notes is optional. created_at is filled in by the database, which is the only place it should be filled in.

That's the value of these constraints — they turn assumptions into rules the database itself enforces.

Common Pitfalls

A short list of things that bite people:

• Explicit NULL defeats DEFAULT. INSERT INTO t (col) VALUES (NULL) won't use the default. The column has to be absent from the column list.
• Expression defaults need parentheses. DEFAULT CURRENT_TIMESTAMP works (it's one of three special keywords). DEFAULT lower(hex(randomblob(8))) does not — wrap it: DEFAULT (lower(hex(randomblob(8)))).
• NOT NULL and the empty string are different. '' is a valid TEXT value and won't trigger the constraint. If you also want to forbid empty strings, that's a job for CHECK (next page).
• ADD COLUMN ... NOT NULL requires a DEFAULT that's not NULL. Without one, SQLite refuses the change.

Next: CHECK Constraints

NOT NULL and DEFAULT cover "must exist" and "fill in if missing." For the next layer of validation — "must be positive," "must be one of these values," "end date must be after start date" — SQLite has CHECK constraints, which let you write arbitrary boolean expressions that every row has to satisfy. That's the next page.