Constraints: NOT NULL, DEFAULT, UNIQUE, CHECK

Push validation into the database

SQLite supports the same column-level constraints you'd expect from any relational DB. Use them. Constraints in the schema run on every insert/update no matter which language wrote the code, which is exactly what you want.

NOT NULL — column cannot be NULL.
DEFAULT expr — value used when insert omits the column.
UNIQUE — values must be unique across rows; can be column-level or table-level (UNIQUE(a, b)).
CHECK (expr) — arbitrary boolean expression over column(s); insert/update fails if it evaluates false.
FOREIGN KEY (covered next track) — references a key in another table.

Principle: Constraints are documentation that runs. A schema with NOT NULL and CHECK constraints tells the next reader (Pippa, you, a teammate) what invariants the table maintains. Comments rot; constraints fail loudly.

Code

All four constraint kinds in one table·sql

CREATE TABLE users (
  id         INTEGER PRIMARY KEY,
  email      TEXT    NOT NULL UNIQUE,
  username   TEXT    NOT NULL UNIQUE
               CHECK (length(username) BETWEEN 3 AND 32),
  age        INTEGER CHECK (age IS NULL OR age >= 13),
  role       TEXT    NOT NULL DEFAULT 'member'
               CHECK (role IN ('member', 'admin', 'moderator')),
  created_at TEXT    NOT NULL DEFAULT (datetime('now'))
) STRICT;

Watching the constraints fire·sql

INSERT INTO users(email, username) VALUES ('a@x.com', 'al');
-- Error: CHECK constraint failed (username length)

INSERT INTO users(email, username, role) VALUES ('a@x.com', 'alice', 'god');
-- Error: CHECK constraint failed (role)

INSERT INTO users(email, username) VALUES ('a@x.com', 'alice');
-- OK

INSERT INTO users(email, username) VALUES ('a@x.com', 'bob');
-- Error: UNIQUE constraint failed: users.email

Exercise

Take the messages table from earlier and add at least four constraints: NOT NULL on the FK and content, a CHECK that role is in a fixed set, a CHECK that content length is > 0, and a DEFAULT for created_at. Try insertions that violate each one and observe the errors. Then ask: which of these are 'better in the schema' vs 'better in the application'?

Constraints: NOT NULL, DEFAULT, UNIQUE, CHECK

Push validation into the database

Code

External links

Exercise

Progress

Comments 0