Chapter 11 · Defining Databases and Tables
PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, and DEFAULT
Constraints are executable assumptions. They move critical rules from documentation and application code into the database boundary, where every writer must obey them.
Learning outcomes
Constraints define the legal state space of a table. They should express stable rules that every data writer must obey, independent of programming language, application version, or import tool.
Distinguish row identity, alternate identity, references, domains, and defaults.
Design single-column and composite primary and foreign keys.
Use UNIQUE, NOT NULL, CHECK, and DEFAULT without confusing their roles.
Choose referential actions that match lifecycle semantics.
Test both successful writes and intentional constraint failures.
The constraint map
Each constraint answers a different integrity question. Combining them creates a stronger contract than any one constraint alone.
| Constraint | Rule | Typical use |
|---|---|---|
| PRIMARY KEY | Every row has one non-null unique identity. | department_id or (course_id, student_id). |
| UNIQUE | No two rows share the same alternate key. | email, SKU, or department-scoped course code. |
| FOREIGN KEY | A child reference matches an allowed parent key. | course.department_id → department.department_id. |
| NOT NULL | The fact must be present. | course.title or enrollment.status. |
| CHECK | A row-level Boolean condition must not be false. | credits BETWEEN 1 AND 6. |
| DEFAULT | Supply a value when the writer omits the column. | state = queued or created_at = current time. |
Primary and alternate keys
CREATE TABLE account ( account_id INTEGER PRIMARY KEY, public_id TEXT NOT NULL UNIQUE, email TEXT NOT NULL UNIQUE, display_name TEXT NOT NULL) STRICT;INSERT INTO account (public_id, email, display_name)VALUES ('usr_01JXYZ', 'nadia@example.com', 'Nadia');The surrogate primary key supports internal relationships. The public ID and email remain candidate keys and need explicit uniqueness if the business depends on them.
Composite keys preserve relationship grain
CREATE TABLE enrollment_rule ( course_id INTEGER NOT NULL, student_id INTEGER NOT NULL, status TEXT NOT NULL DEFAULT 'enrolled', PRIMARY KEY (course_id, student_id)) STRICT, WITHOUT ROWID;-- This second row fails because the pair already exists.INSERT INTO enrollment_rule VALUES (100, 1001, 'enrolled');INSERT INTO enrollment_rule VALUES (100, 1001, 'completed');If history is required, the grain must change—for example by adding an attempt number or effective timestamp—rather than weakening the key and silently permitting duplicates.
Foreign keys and referential actions
PRAGMA foreign_keys = ON;CREATE TABLE parent_course ( course_id INTEGER PRIMARY KEY, title TEXT NOT NULL) STRICT;CREATE TABLE child_enrollment ( course_id INTEGER NOT NULL, student_id INTEGER NOT NULL, status TEXT NOT NULL DEFAULT 'enrolled', PRIMARY KEY (course_id, student_id), FOREIGN KEY (course_id) REFERENCES parent_course(course_id) ON UPDATE CASCADE ON DELETE CASCADE) STRICT, WITHOUT ROWID;| Action | Meaning | Use only when |
|---|---|---|
| RESTRICT / NO ACTION | Reject a parent change that leaves dependent children invalid. | Children must be handled explicitly. |
| CASCADE | Propagate parent update or deletion to children. | The child has no independent lifecycle. |
| SET NULL | Keep the child but remove its optional reference. | The child column is nullable and “unassigned” is meaningful. |
| SET DEFAULT | Replace the reference with its default. | The default identifies a valid parent and the semantics are explicit. |
Composite foreign keys
CREATE TABLE catalog_course ( department_code TEXT NOT NULL, course_code TEXT NOT NULL, title TEXT NOT NULL, PRIMARY KEY (department_code, course_code)) STRICT, WITHOUT ROWID;CREATE TABLE catalog_section ( department_code TEXT NOT NULL, course_code TEXT NOT NULL, section_no INTEGER NOT NULL, PRIMARY KEY (department_code, course_code, section_no), FOREIGN KEY (department_code, course_code) REFERENCES catalog_course(department_code, course_code)) STRICT, WITHOUT ROWID;A child must reference a parent primary key or a parent key protected by a compatible UNIQUE constraint. The column order and cardinality must match.
NOT NULL, CHECK, and SQL truth
CREATE TABLE grading_policy ( policy_id INTEGER PRIMARY KEY, policy_name TEXT NOT NULL CHECK (length(trim(policy_name)) > 0), pass_percent REAL NOT NULL CHECK (pass_percent BETWEEN 0 AND 100), late_penalty REAL NOT NULL DEFAULT 0 CHECK (late_penalty BETWEEN 0 AND 100), effective_from TEXT NOT NULL CHECK (date(effective_from) IS NOT NULL), effective_to TEXT, CHECK ( effective_to IS NULL OR date(effective_to) >= date(effective_from) )) STRICT;A SQLite CHECK constraint fails when its expression is false or zero. If the expression evaluates to NULL, it is not false, so required inputs also need NOT NULL when missing data is forbidden.
Defaults do not validate explicit input
CREATE TABLE task ( task_id INTEGER PRIMARY KEY, task_name TEXT NOT NULL, state TEXT NOT NULL DEFAULT 'queued' CHECK (state IN ('queued', 'running', 'done'))) STRICT;-- Uses the default.INSERT INTO task (task_name) VALUES ('refresh catalog');-- Fails: explicit NULL does not request the default.INSERT INTO task (task_name, state) VALUES ('publish report', NULL);A default is a value-generation rule. NOT NULL and CHECK are validation rules. Use all three when all three semantics are required.
Reusable Chapter 11 practice schema
Run this SQLite script in a disposable database before the hands-on exercises. It establishes a small academic domain with strict tables, generated data, composite uniqueness, foreign keys, a view, and representative rows.
PRAGMA foreign_keys = ON;DROP VIEW IF EXISTS active_course_catalog;DROP TABLE IF EXISTS enrollment;DROP TABLE IF EXISTS course;DROP TABLE IF EXISTS instructor;DROP TABLE IF EXISTS department;CREATE TABLE department ( department_id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE, name TEXT NOT NULL UNIQUE, budget_cents INTEGER NOT NULL DEFAULT 0 CHECK (budget_cents >= 0), created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP) STRICT;CREATE TABLE instructor ( instructor_id INTEGER PRIMARY KEY, department_id INTEGER NOT NULL REFERENCES department(department_id), email TEXT NOT NULL UNIQUE, full_name TEXT NOT NULL, hired_on TEXT NOT NULL CHECK (date(hired_on) IS NOT NULL), active INTEGER NOT NULL DEFAULT 1 CHECK (active IN (0, 1))) STRICT;CREATE TABLE course ( course_id INTEGER PRIMARY KEY, department_id INTEGER NOT NULL REFERENCES department(department_id), instructor_id INTEGER REFERENCES instructor(instructor_id) ON DELETE SET NULL, course_code TEXT NOT NULL, title TEXT NOT NULL, credits INTEGER NOT NULL DEFAULT 3 CHECK (credits BETWEEN 1 AND 6), capacity INTEGER NOT NULL DEFAULT 30 CHECK (capacity > 0), published INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0, 1)), display_name TEXT GENERATED ALWAYS AS (course_code || ' · ' || title) VIRTUAL, created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, UNIQUE (department_id, course_code)) STRICT;CREATE TABLE enrollment ( course_id INTEGER NOT NULL REFERENCES course(course_id) ON DELETE CASCADE, student_id INTEGER NOT NULL, enrolled_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP, status TEXT NOT NULL DEFAULT 'enrolled' CHECK (status IN ('enrolled', 'completed', 'withdrawn')), PRIMARY KEY (course_id, student_id)) STRICT, WITHOUT ROWID;CREATE VIEW active_course_catalog ASSELECT c.course_id, d.code AS department_code, c.course_code, c.title, c.credits, c.capacityFROM course AS cJOIN department AS d ON d.department_id = c.department_idWHERE c.published = 1;INSERT INTO department (department_id, code, name, budget_cents) VALUES (1, 'DATA', 'Data Engineering', 25000000), (2, 'CS', 'Computer Science', 30000000);INSERT INTO instructor (instructor_id, department_id, email, full_name, hired_on)VALUES (10, 1, 'nadia@example.edu', 'Nadia Rahimi', '2024-09-01'), (11, 2, 'omar@example.edu', 'Omar Haddad', '2023-02-15');INSERT INTO course (course_id, department_id, instructor_id, course_code, title, credits, capacity, published)VALUES (100, 1, 10, 'SQL-101', 'SQL Foundations', 3, 40, 1), (101, 1, 10, 'DE-201', 'Data Pipelines', 4, 30, 1), (102, 2, 11, 'DB-220', 'Database Systems',4, 35, 0);INSERT INTO enrollment (course_id, student_id, status) VALUES (100, 1001, 'enrolled'), (100, 1002, 'completed'), (101, 1001, 'enrolled');Constraint laboratory
INSERT INTO course ( course_id, department_id, instructor_id, course_code, title, credits, capacity, published)VALUES (103, 1, 10, 'SQL-201', 'Advanced SQL', 4, 28, 1);INSERT INTO enrollment (course_id, student_id)VALUES (103, 1003);SELECT c.display_name, e.student_id, e.statusFROM course AS cJOIN enrollment AS e ON e.course_id = c.course_idWHERE c.course_id = 103;-- Duplicate department-scoped course code.INSERT INTO course (course_id, department_id, course_code, title)VALUES (104, 1, 'SQL-201', 'Duplicate Code');-- Missing parent department.INSERT INTO course (course_id, department_id, course_code, title)VALUES (105, 999, 'SQL-999', 'Orphan Course');-- Invalid domain value.INSERT INTO course (course_id, department_id, course_code, title, credits)VALUES (106, 1, 'SQL-000', 'Zero Credit Course', 0);Constraint testing strategy
Valid examples
Prove representative legal values can be stored.
Edges
Test minimum, maximum, empty, NULL, and exact-key boundaries.
Illegal states
Verify each constraint rejects the intended violation.
Parent actions
Test delete and update actions with real child rows.
Checkpoint
Choose the constraint
- Why can a table have only one primary key but several candidate keys?
- When is a composite key preferable to a generated ID?
- Why must foreign-key enforcement be enabled explicitly in SQLite connections?
- Why can CHECK alone fail to reject NULL?
- What is the semantic difference between CASCADE and SET NULL?
Review the answers
The primary key is the chosen row identity; other candidate keys use UNIQUE. Composite keys preserve a naturally multi-column grain. SQLite enforcement is connection-configurable. CHECK permits unknown results, so required values need NOT NULL. CASCADE removes or changes dependent children, while SET NULL preserves an optional child with no parent reference.
Summary and references
- Constraints define legal database states.
- Keys protect identity and relationship grain.
- Foreign-key actions must match real lifecycle ownership.
- Defaults generate omitted values; they do not replace validation.
- Constraint tests should include valid, boundary, and deliberately invalid writes.