Constraints

A constraint is a rule attached to a column that SQLite enforces on every insert or update, so bad data never makes it into the table in the first place. Instead of checking "is this title empty?" or "is this rating in range?" in application code every single time, you declare the rule once in the schema and let the database reject anything that breaks it. In this lesson we tighten up the recipes table from the RecipeBox app with four constraints: NOT NULL, UNIQUE, CHECK, and DEFAULT.

NOT NULL — Require a Value

A column marked NOT NULL can never store NULL. Every row must supply a real value for it, or the insert fails. It's the right choice for anything a recipe simply can't exist without — a title, for example.

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

-- Rejected: title is missing
INSERT INTO recipes (user_id, title) VALUES (1, NULL);
-- Error: NOT NULL constraint failed: recipes.title

UNIQUE — No Duplicate Values

A UNIQUE constraint guarantees that no two rows share the same value in that column (multiple NULLs are still allowed, since NULL never equals anything, including another NULL). It's a natural fit for a recipe's URL-friendly slug, which the app uses to build shareable links like /recipes/creamy-garlic-pasta.

CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  slug TEXT UNIQUE
);

INSERT INTO recipes (title, slug) VALUES ('Creamy Garlic Pasta', 'creamy-garlic-pasta');

-- Rejected: slug already exists
INSERT INTO recipes (title, slug) VALUES ('Garlic Pasta, Creamy', 'creamy-garlic-pasta');
-- Error: UNIQUE constraint failed: recipes.slug

CHECK — Custom Validation Rules

CHECK lets you write an arbitrary boolean expression that every row must satisfy. It's the most flexible constraint: use it to restrict a column to a fixed set of values, keep a number within a sensible range, or enforce any rule SQLite can express as an expression.

CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  difficulty TEXT CHECK(difficulty IN ('easy', 'medium', 'hard')),
  prep_minutes INTEGER CHECK(prep_minutes > 0)
);

-- Rejected: 'extreme' is not one of the allowed values
INSERT INTO recipes (title, difficulty) VALUES ('Volcano Chili', 'extreme');
-- Error: CHECK constraint failed: recipes

DEFAULT — Fallback Values

DEFAULT supplies a value automatically whenever an insert doesn't specify one for that column. It's commonly used for timestamps and for settling on a sensible starting value, like assuming a recipe is 'medium' difficulty until someone says otherwise.

CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  difficulty TEXT DEFAULT 'medium',
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

-- difficulty and created_at are filled in automatically
INSERT INTO recipes (title) VALUES ('Weeknight Fried Rice');

SELECT title, difficulty, created_at FROM recipes WHERE title = 'Weeknight Fried Rice';
-- Weeknight Fried Rice | medium | 2026-08-27 09:14:02

Putting It Together: The RecipeBox recipes Table

Here's the full picture — a recipes table with all four constraints working side by side, plus the foreign key from the previous lesson tying each recipe back to its author in users.

CREATE TABLE recipes (
  id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id),
  title TEXT NOT NULL,
  slug TEXT UNIQUE,
  difficulty TEXT CHECK(difficulty IN ('easy', 'medium', 'hard')) DEFAULT 'medium',
  prep_minutes INTEGER CHECK(prep_minutes > 0),
  created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

Every recipe now must belong to a real user, must have a title, gets a unique shareable slug if one is provided, defaults to 'medium' difficulty within an allowed set of values, rejects a nonsensical prep time, and automatically records when it was created.

What Happens When a Constraint Is Violated

By default, SQLite aborts the offending statement and rolls back any partial changes it made, leaving the rest of the database untouched. The application sees an error naming the exact constraint that failed, which is exactly what you want — a clear signal instead of silent bad data.