Constraints, Primary Keys & Foreign Keys

Types answer one question about a column: what shape of value lives there. They do not answer the harder question — which values are allowed. A salary column of type INTEGER will happily store -50000, 0, or a number nobody ever negotiated, because the type only promises the value is a whole number.

Constraints are the rules layered on top of types that reject bad rows before they are stored. They turn a permissive column into a dependable one: a salary that cannot be negative, an email that cannot be blank, a username that cannot be duplicated. Each constraint is a small guarantee about your data, and together they are the difference between a database you trust and one you constantly clean up after. This lesson adds the five constraints you will use every day.

flowchart TD
  V["a new row arrives"] --> T{"right TYPE?"}
  T -->|"yes"| N{"NOT NULL<br/>columns filled?"}
  N -->|"yes"| U{"UNIQUE<br/>no duplicates?"}
  U -->|"yes"| C{"CHECK<br/>rules pass?"}
  C -->|"yes"| K["row stored"]
  T -->|"no"| X["rejected"]
  N -->|"no"| X
  U -->|"no"| X
  C -->|"no"| X
  style K fill:#0e7490,color:#fff
  style X fill:#b45309,color:#fff

Constraints are tested in turn as a row arrives. Fail any one and the whole row is rejected.

NOT NULL and UNIQUE

The two simplest constraints cover the two most common mistakes. NOT NULL forbids the missing value entirely — the column must be supplied on every insert. UNIQUE forbids duplicates across the whole column — no two rows may share the value.

NOT NULL on a name stops a faceless customer from ever entering the database. UNIQUE on a username stops two accounts from claiming the same handle. Add both and you have a column that is always present and always distinct, which is exactly what an identifier needs.

You write them inline, right after the type: email TEXT NOT NULL UNIQUE. Several constraints can stack on one column, separated by spaces, and SQLite checks each in turn as a row arrives, rejecting the insert if any fails.

An accounts table with a required email and a unique username. Both rows are valid.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL
);

INSERT INTO accounts (id, username, email) VALUES
    (1, 'ada', 'ada@example.com'),
    (2, 'bo', 'bo@example.com');

SELECT username, email FROM accounts;

CHECK: a rule you write yourself

NOT NULL and UNIQUE are fixed rules. A CHECK constraint is one you write yourself, as any expression that must evaluate to true for the row to be accepted. salary INTEGER CHECK (salary > 0) rejects negative pay. quantity INTEGER CHECK (quantity >= 0) rejects negative stock. role TEXT CHECK (role IN ('Engineer', 'Manager', 'Sales')) rejects a role you never defined.

The expression can even reference several columns at once: CHECK (end_on >= start_on) enforces that a period ends after it begins. Whatever you can phrase as a boolean condition, you can pin to the table, and the database will test every row against it for the rest of time with no further effort from you.

flowchart TD
  G{"CHECK (salary > 0)"}
  G -->|"salary = -500: false"| D["rejected"]
  G -->|"salary = 720000: true"| K["stored"]
  style K fill:#0e7490,color:#fff
  style D fill:#b45309,color:#fff

A CHECK tests each prospective row: the condition is true, the row is stored; false, it is thrown back.

A CHECK that keeps salaries positive and quantities non-negative.

CREATE TABLE stock (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    salary INTEGER CHECK (salary > 0),
    quantity INTEGER CHECK (quantity >= 0)
);

INSERT INTO stock (id, name, salary, quantity) VALUES
    (1, 'Wrench', 480000, 40),
    (2, 'Hammer', 520000, 0);

SELECT name, salary, quantity FROM stock;

Primary keys and foreign keys

A primary key is the column that uniquely identifies a row — conventionally the id. Declaring PRIMARY KEY bundles three guarantees: the column is NOT NULL, its values are UNIQUE, and the database builds a fast lookup around it. In the seed database, departments.id, employees.id, and projects.id are all primary keys.

A foreign key is how one table points at a row in another. employees.department_id is a foreign key that references departments.id — every employee's department must be one that actually exists. The constraint ties the two tables together: you cannot assign an employee to department 999 if no such department exists, and the relationship is upheld by the database rather than merely hoped for by the application.

flowchart LR
  D["departments<br/>id = PRIMARY KEY"] -->|"employees.department_id<br/>references departments.id"| E["employees<br/>department_id = FOREIGN KEY"]
  style D fill:#0e7490,color:#fff
  style E fill:#b45309,color:#fff

A foreign key in one table references the primary key of another, tying the rows together.

Enforcement is OFF by default. department_id 99 does not exist, yet the orphan row is accepted.

PRAGMA foreign_keys = OFF;  -- SQLite's default

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

CREATE TABLE roster (
    id INTEGER PRIMARY KEY,
    team_id INTEGER,
    FOREIGN KEY (team_id) REFERENCES teams (id)
);

-- team_id 99 exists in no team, but this succeeds because enforcement is off:
INSERT INTO roster (id, team_id) VALUES (1, 99);

SELECT id, team_id FROM roster;

Constraints guard the door, not the room

A constraint is checked the moment a row is written — never afterwards. That has a quiet consequence: adding a constraint to a table that already holds data does not clean up the bad rows already there. If a thousand negative salaries already exist, adding CHECK (salary > 0) may be refused outright because the existing rows violate the new rule.

The practical lesson is to design constraints in at the start, when the table is empty and the rules are cheap to set. Retrofitting them onto a live table means first auditing and repairing the data, then adding the rule. Constraints prevent future mistakes; they do not undo past ones.

Exercise

Create an accounts table with an id (INTEGER PRIMARY KEY), a username of type TEXT that is both NOT NULL and UNIQUE, and an email of type TEXT that is NOT NULL.

CREATE TABLE accounts (
    -- three columns, two of them constrained
);

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL
);

Exercise

You want to guarantee that no two rows ever share the same email. Which constraint does that?

Exercise

This staff table has a malformed CHECK — the parenthesis is never closed, so it will not run. Fix it so salary must be greater than zero, leaving everything else unchanged.

CREATE TABLE staff (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    salary INTEGER CHECK (salary > 0
);

CREATE TABLE staff (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    salary INTEGER CHECK (salary > 0)
);

Exercise

Imagine a new rule: every salary must be at least 500000. List the name and salary of the employees who would violate that CHECK today, lowest salary first.

SELECT name, salary
FROM employees
-- which rows would fail the rule?
;

SELECT name, salary FROM employees WHERE salary < 500000 ORDER BY salary ASC;

Exercise

Design a tasks table for project work. It needs an id (INTEGER PRIMARY KEY), a project_id (INTEGER) that references projects(id) as a foreign key, a title (TEXT that cannot be empty), and a budget (INTEGER) that must be zero or more via a CHECK.

CREATE TABLE tasks (
    -- four columns; include the FK and the CHECK
);

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    project_id INTEGER,
    title TEXT NOT NULL,
    budget INTEGER CHECK (budget >= 0),
    FOREIGN KEY (project_id) REFERENCES projects (id)
);

Recap

Types set the shape of a value; constraints set which values are allowed, and reject bad rows at insert time.
NOT NULL forbids missing values; UNIQUE forbids duplicates; both stack inline after the type.
CHECK (expression) lets you write any boolean rule — a range, a fixed list, or a cross-column condition.
PRIMARY KEY means not-null, unique, and indexed — the identity of the row.
FOREIGN KEY ... REFERENCES ties a column to another table's primary key, but in SQLite it is not enforced unless PRAGMA foreign_keys = ON.

Next you will speed up the queries that read these well-designed tables, with indexes.

Constraints, Primary Keys & Foreign Keys

NOT NULL and UNIQUE

CHECK: a rule you write yourself

Primary keys and foreign keys

Constraints guard the door, not the room

Recap

Checkpoint quiz

Go deeper — technical resources