Learn

Constraints are rules the database enforces on every INSERT/UPDATE. They're how you turn schema into a contract — invalid data can't get in.

PRIMARY KEY

Uniquely identifies each row. Auto-creates an index. One per table.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT
);

In SQLite, INTEGER PRIMARY KEY is auto-incrementing — leave it NULL on insert and SQLite assigns the next available value.

Composite primary key — multiple columns together:

CREATE TABLE order_items (
    order_id INTEGER,
    product_id INTEGER,
    quantity INTEGER,
    PRIMARY KEY (order_id, product_id)
);

The pair (order_id, product_id) must be unique — but each alone may repeat.

FOREIGN KEY

Refers to another table's primary key. Enforces referential integrity:

CREATE TABLE users (id INTEGER PRIMARY KEY, name TEXT);

CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER REFERENCES users(id),    -- short form
    title TEXT
);

-- Or named separately:
CREATE TABLE posts (
    id INTEGER PRIMARY KEY,
    user_id INTEGER,
    title TEXT,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

With FK enforcement on (PRAGMA foreign_keys = ON; in SQLite), you can't insert a post with a user_id that doesn't exist in users.

ON DELETE actions — what happens when the referenced row is deleted:

• ON DELETE NO ACTION — refuse the delete; this is the default. (RESTRICT is a separate, stricter option that also blocks the delete.)
• ON DELETE CASCADE — delete the dependent rows too
• ON DELETE SET NULL — set the FK column to NULL
• ON DELETE SET DEFAULT — set to the column's default

NOT NULL

Force a column to always have a value:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL,
    age INTEGER
);

INSERT without email fails. NULL is forbidden.

UNIQUE

Value must not appear in any other row:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT UNIQUE,
    name TEXT
);

Multiple UNIQUE constraints can coexist with one PRIMARY KEY. Use UNIQUE for natural keys (email, username, slug).

Composite UNIQUE:

UNIQUE (year, month, slug)

CHECK

Any boolean expression on the row's data:

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT,
    price REAL CHECK (price >= 0),
    stock INTEGER CHECK (stock >= 0),
    CHECK (length(name) > 0)              -- table-level CHECK
);

INSERT or UPDATE that violates a CHECK fails. Use CHECK for invariants the type system can't express.

DEFAULT

Value used when INSERT doesn't specify the column:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    name TEXT,
    created_at TEXT DEFAULT CURRENT_TIMESTAMP,
    role TEXT DEFAULT 'member'
);

Putting it together

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    total REAL NOT NULL CHECK (total >= 0),
    status TEXT NOT NULL DEFAULT 'pending'
        CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
    UNIQUE (user_id, created_at)
);

This schema makes broken data impossible. Every constraint is a class of bug the database catches before it becomes an outage.

Constraints have a cost — every INSERT/UPDATE checks them. For hot insert paths, sometimes constraints are relaxed and validation moves to the application layer. But by default: more constraints = fewer bugs.