Constraints And Keys

A database that lets you store anything anywhere is not very useful in practice. What stops someone from inserting an order for a customer that does not exist? What prevents two users from sharing the same email address? What keeps a product price from being set to a negative number?

The answer is constraints. Constraints are rules you attach to a table or column that the database engine enforces automatically, before any data reaches disk. If an insert or update would violate a constraint, the database rejects it with an error. This means you get data integrity for free — you do not have to write application-layer checks for every rule you care about.

This lesson covers the five constraints you will use most often: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK. It also covers DEFAULT values, which are not strictly constraints but work alongside them.

What You'll Learn

By the end of this lesson you will write a schema whose rules the database actually refuses to break, and — just as important — you will be able to tell the difference between a constraint that is being enforced and one that is merely written down. That distinction is not academic here: on this engine, one of the five constraints is switched off by default, so a schema that looks airtight silently accepts the exact rows it was written to reject. You will make each rule fire, read the error it produces, and know which line turned the checking on.

This is the integrity-rules milestone the warehouse capstone is built on. The capstone's schema declares keys between customers, orders and line items, and every report that joins them is trusting those declarations; if they are not enforced, an orphaned order quietly disappears from one report and appears in another. You arrive able to join tables on a key (Joins and Relationships) and to design a normalized schema (Database Design); what is new is making the database itself refuse the data that would break those joins.

Primary Keys

Every table should have a primary key — one column (or combination of columns) whose value uniquely identifies each row. On this engine an INTEGER PRIMARY KEY is enforced as both unique and non-null. Be aware of one wrinkle you will meet again under UNIQUE: for a non-integer primary key, such as a TEXT PRIMARY KEY, SQLite enforces uniqueness but not the not-null half — a table with a TEXT PRIMARY KEY will accept two rows whose key is NULL. That is a long-standing SQLite behaviour rather than a rule of SQL, and the fix is to write NOT NULL on the column explicitly.

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  name        TEXT    NOT NULL,
  price_cents INTEGER NOT NULL
);

INSERT INTO products VALUES (1, 'Keyboard', 7999);
INSERT INTO products VALUES (2, 'Mouse', 2999);
INSERT INTO products VALUES (3, 'Monitor', 34900);

SELECT id, name, price_cents FROM products ORDER BY id;

Money is an INTEGER count of cents here, and throughout this lesson, for the reason established in Data Types and Casting: a DECIMAL(10,2) column is stored as a float on this engine, so prices written that way accumulate rounding error the moment you total them.

Now add a duplicate id and watch the database refuse it:

CREATE TABLE products (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  price_cents INTEGER NOT NULL
);

INSERT INTO products VALUES (1, 'Keyboard', 7999);
INSERT INTO products VALUES (1, 'Mouse', 2999);

The first insert lands and reports one row affected; the second is rejected outright, and the lane prints this on the error stream:

Error: UNIQUE constraint failed: products.id

Two details in that run are worth noticing, because they are how constraint failures behave generally on this lane. The rejection is per statement, not per script: the Keyboard row was already written and stays written. And the script's exit status is non-zero at the end even though most of it succeeded — so a failed constraint is something your application must actually check for, not something you will notice from the output alone.

In practice, most databases support auto-incrementing primary keys so you never have to supply the value manually. In SQLite the syntax is INTEGER PRIMARY KEY, which reuses the table's internal row id and assigns the next value automatically when you omit it (SQLite documentation: CREATE TABLE, ROWIDs and the INTEGER PRIMARY KEY). PostgreSQL spells the same idea as GENERATED ALWAYS AS IDENTITY (PostgreSQL 16 documentation: CREATE TABLE), which is the SQL-standard form, and also keeps its own PostgreSQL-specific SERIAL types (PostgreSQL 16 documentation: Numeric Types, Serial Types).

Foreign Keys

A foreign key links a column in one table to the primary key of another table. The rule it is meant to enforce is referential integrity: a child row may not point at a parent that does not exist, and a parent that is still being pointed at may not simply vanish.

And here is the single most important fact in this lesson: on SQLite, that rule is switched off unless you switch it on. The SQLite documentation states it plainly — "foreign key constraints are disabled by default (for backwards compatibility), so must be enabled separately for each database connection" (SQLite documentation: Foreign Key Support, enabling enforcement). A REFERENCES clause is always parsed and stored, and with enforcement off it is simply documentation: the engine reads it, records it, and checks nothing.

Every Run on this page starts a fresh connection with enforcement off, so every fence that intends to demonstrate foreign keys must turn it on for itself. That is why PRAGMA foreign_keys = ON; is the visible first line of the fences below, and why you should treat its absence in someone else's schema as a live question rather than an oversight.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), total_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1,'Alice'),(2,'Bob');
INSERT INTO orders VALUES (101,1,14999);
INSERT INTO orders VALUES (103,99,22000);
SELECT COUNT(*) AS order_count FROM orders;

With the pragma on, the same schema behaves the way the declaration reads:

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  id    INTEGER PRIMARY KEY,
  name  TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total_cents INTEGER NOT NULL,
  placed_at   TEXT NOT NULL
);

INSERT INTO customers VALUES (1, 'Alice Chen', 'alice@example.com');
INSERT INTO customers VALUES (2, 'Bob Patel', 'bob@example.com');

INSERT INTO orders VALUES (101, 1, 14999, '2024-01-15');
INSERT INTO orders VALUES (102, 2, 5950,  '2024-01-16');
INSERT INTO orders VALUES (103, 1, 22000, '2024-01-17');

SELECT o.id, c.name, o.total_cents
FROM orders o
JOIN customers c ON c.id = o.customer_id
ORDER BY o.id;

Now try the violation. This fence is the same schema with the pragma on and one order pointing at a customer who does not exist:

PRAGMA foreign_keys = ON;

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(id),
  total_cents INTEGER NOT NULL
);

INSERT INTO customers VALUES (1, 'Alice Chen');
INSERT INTO orders VALUES (103, 99, 22000);

Error: FOREIGN KEY constraint failed

Two things to note about that message. It names no column and no value — you are told a foreign key failed, not which one, so a script inserting into several tables gives you a hunt rather than a location. And it arrives only because of the pragma: delete that first line and the identical script exits 0 with the orphan safely stored.

Try one modification before moving on. Take the fence above, delete the PRAGMA line, and add SELECT COUNT(*) AS orders FROM orders; at the end. Predict first: the insert now succeeds, the count reports 1, and the exit status is clean — the same script, the same schema, the opposite outcome, decided entirely by a line that says nothing about orders at all.

ON DELETE Behavior

You can control what happens to child rows when a parent row is deleted:

• ON DELETE CASCADE — deleting the parent automatically deletes all child rows
• ON DELETE SET NULL — child rows have the foreign key column set to NULL
• ON DELETE RESTRICT — deletion is blocked if any child rows exist

None of these fire unless enforcement is on: with the pragma off, the parent row is deleted and the children are left exactly as they were, pointing at nothing. Note also that RESTRICT is not the default — SQLite's documentation states that "if an action is not explicitly specified, it defaults to NO ACTION" (SQLite documentation: Foreign Key Support, ON DELETE and ON UPDATE actions). For a delete, NO ACTION and RESTRICT both end up preventing the deletion; they differ in when the check happens, which becomes visible only inside a deferred constraint or a multi-statement update.

PRAGMA foreign_keys = ON;

CREATE TABLE departments (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE employees (
  id            INTEGER PRIMARY KEY,
  name          TEXT NOT NULL,
  department_id INTEGER REFERENCES departments(id) ON DELETE SET NULL
);

INSERT INTO departments VALUES (10, 'Engineering');
INSERT INTO departments VALUES (20, 'Marketing');

INSERT INTO employees VALUES (1, 'Diana', 10);
INSERT INTO employees VALUES (2, 'Marco', 10);
INSERT INTO employees VALUES (3, 'Sara',  20);

DELETE FROM departments WHERE id = 10;

SELECT id, name, department_id FROM employees ORDER BY id;

After deleting the Engineering department, Diana and Marco still exist and their department_id has been set to NULL, which prints as None. Sara is untouched. Remove the pragma line and rerun: Diana and Marco keep the value 10, still pointing at a department that no longer exists — the ON DELETE SET NULL clause never fires, because the machinery that would fire it is switched off.

Unique Constraints

A UNIQUE constraint ensures no two rows share the same value in a column (or combination of columns). Unlike a primary key, a unique column can contain NULL — and SQLite treats each NULL as distinct from every other, so multiple NULL values are allowed in a UNIQUE column. This follows from what you saw in Null Handling: two unknowns cannot be shown to be equal, so the engine has no grounds to call them duplicates. PostgreSQL behaves the same way by default and offers NULLS NOT DISTINCT to opt out (PostgreSQL 16 documentation: Constraints, unique constraints and null values), so if you need the column to hold at most one missing value, say so with NOT NULL and a sentinel rather than assuming the constraint covers it.

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

INSERT INTO users VALUES (1, 'alice',  'alice@example.com');
INSERT INTO users VALUES (2, 'bob',    'bob@example.com');

SELECT id, username, email FROM users ORDER BY id;

Try inserting a duplicate username to see the constraint fire:

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

INSERT INTO users VALUES (1, 'alice', 'alice@example.com');
INSERT INTO users VALUES (2, 'alice', 'other@example.com');

Error: UNIQUE constraint failed: users.username

Unlike the foreign-key message, this one names the exact column that was violated — which is worth knowing, because a row breaking two unique constraints at once reports only the first one the engine happens to check.

You can also create composite unique constraints — for example, ensuring a user can only review each product once. Note what this fence prints when you run it: nothing at all. A CREATE TABLE produces no output on this lane, so an empty result pane here means the statement succeeded, not that something went wrong.

CREATE TABLE reviews (
  user_id    INTEGER NOT NULL,
  product_id INTEGER NOT NULL,
  rating     INTEGER NOT NULL,
  UNIQUE (user_id, product_id)
);

CREATE TABLE tags (id INTEGER PRIMARY KEY, label TEXT NOT NULL UNIQUE);
INSERT INTO tags VALUES (1, 'new');
INSERT INTO tags VALUES (2, 'sale');
CREATE INDEX idx_tags_label ON tags(label);

NOT NULL

By default, any column can store NULL unless you explicitly prevent it. NOT NULL makes a column required — every insert and update must supply a real value.

This is important because NULL has unusual behavior in SQL: it is not equal to anything, including itself. Forgetting a NOT NULL constraint on a column you intend to always have a value leads to confusing query results and extra defensive code.

CHECK Constraints

CHECK lets you write an arbitrary condition that every row must satisfy. If the condition evaluates to false, the insert or update is rejected.

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents > 0),
  stock       INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0),
  category    TEXT NOT NULL CHECK (category IN ('electronics', 'clothing', 'food'))
);

INSERT INTO products VALUES (1, 'Laptop', 99900, 5, 'electronics');
INSERT INTO products VALUES (2, 'T-Shirt', 2499, 50, 'clothing');

SELECT id, name, price_cents, stock, category FROM products ORDER BY id;

Unlike foreign keys, CHECK constraints are enforced with no pragma required — they are on by default. Now try inserting a product with a negative price:

CREATE TABLE products (
  id          INTEGER PRIMARY KEY,
  name        TEXT NOT NULL,
  price_cents INTEGER NOT NULL CHECK (price_cents > 0),
  category    TEXT NOT NULL CHECK (category IN ('electronics', 'clothing', 'food'))
);

INSERT INTO products VALUES (1, 'Gadget', -500, 'electronics');

Error: CHECK constraint failed: price_cents > 0

The message quotes the condition that failed, which makes a named set of small checks far easier to debug than one large one. Note that this row violates only the price rule — its category is valid. A row breaking both rules reports only the first failure the engine reaches, so fixing one violation can simply reveal the next.

CREATE TABLE order_lines (
id       INTEGER PRIMARY KEY,
sku      TEXT NOT NULL,
quantity INTEGER CHECK (quantity > 0)
);
INSERT INTO order_lines VALUES (1, 'A-1', 2);
INSERT INTO order_lines VALUES (2, 'B-2', NULL);
INSERT INTO order_lines VALUES (3, 'C-3', 4);
SELECT COUNT(*) AS lines_without_quantity FROM order_lines WHERE quantity IS NULL;

1 row(s) affected
1 row(s) affected
lines_without_quantity
----------------------
0

DEFAULT Values

DEFAULT is not a constraint in the strict sense, but it works alongside them. When an insert omits a column, the database uses the default value instead of NULL.

CREATE TABLE articles (
  id         INTEGER PRIMARY KEY,
  title      TEXT NOT NULL,
  published  INTEGER NOT NULL DEFAULT 0,
  created_at TEXT NOT NULL DEFAULT (date('now'))
);

INSERT INTO articles (id, title) VALUES (1, 'Getting Started with SQL');
INSERT INTO articles (id, title, published) VALUES (2, 'Advanced Joins', 1);

SELECT id, title, published FROM articles ORDER BY id;

The published flag defaults to 0 (draft), and created_at defaults to the date the row was inserted — both without the caller having to supply them.

The query above deliberately leaves created_at out of its SELECT list, and that is a habit worth adopting rather than an omission. Add it and the fence prints whatever today's date happens to be in the sandbox, which runs on UTC: the output changes every day, so any sentence describing it is wrong by tomorrow. A clock-derived default is fine to store; it is a poor thing to assert. When you want to demonstrate or test date behaviour, insert a fixed date explicitly and leave date('now') for the rows real users create.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), total_cents INTEGER NOT NULL);
INSERT INTO customers VALUES (1, 'Ada'), (2, 'Bo');
INSERT INTO orders VALUES (10, 1, 5000);
INSERT INTO orders VALUES (11, 2, 7000);
INSERT INTO orders VALUES (12, 99, 3000);
SELECT COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL;

2 row(s) affected
1 row(s) affected
1 row(s) affected
orphan_orders
-------------
0

Ordering a Schema That Enforces Itself

Enforcement puts the statements of a schema script into a fixed order: a child row cannot reference a parent row that has not been inserted yet, and the check that says so only happens because the pragma ran first. The schema below — the pragma and both tables — is already in place:

PRAGMA foreign_keys = ON;
CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT NOT NULL, author_id INTEGER NOT NULL REFERENCES authors(id));

Try It Yourself

A small library database has been set up with no constraints beyond its primary keys, and two bad rows have already got in: a loan belonging to member 99, who does not exist, and a loan recording minus four copies. This is a build task. The starter below runs and audits the damage, reporting one orphan and one impossible quantity. Your job is to rewrite the schema so neither row can be stored in the first place.

CREATE TABLE members (
id    INTEGER PRIMARY KEY,
name  TEXT NOT NULL,
email TEXT NOT NULL
);
CREATE TABLE loans (
id        INTEGER PRIMARY KEY,
member_id INTEGER,
title     TEXT NOT NULL,
copies    INTEGER
);
-- TODO 1: switch foreign-key enforcement on for this run. It is off by default,
--         so without this line the rest of the schema is only documentation.
-- TODO 2: make member_id a required reference to members(id), so loan 11 for
--         member 99 is refused rather than stored.
-- TODO 3: make copies required and positive, so loan 12 with -4 copies is refused.
--         Remember what a CHECK does with a missing value.
INSERT INTO members VALUES (1, 'Emma Wilson', 'emma@library.org');
INSERT INTO members VALUES (2, 'Luis Torres', 'luis@library.org');
INSERT INTO loans VALUES (10, 1, 'Dune', 2);
INSERT INTO loans VALUES (11, 99, 'Ghost Book', 1);
INSERT INTO loans VALUES (12, 2, 'Solaris', -4);
SELECT
(SELECT COUNT(*) FROM loans l LEFT JOIN members m ON m.id = l.member_id WHERE m.id IS NULL) AS orphan_loans,
(SELECT COUNT(*) FROM loans WHERE copies IS NULL OR copies < 0) AS bad_copies;

1 row(s) affected
1 row(s) affected
1 row(s) affected
orphan_loans | bad_copies
-------------------------
0 | 0

Before you submit the version that passes, try two variations on it in the block's editor — an unsolved run is never limited, and each one shows you its output beside the expected lines. Predict each before you run it, then put the passing version back and run that last. First, delete only the PRAGMA line and leave the rest of your schema intact: the REFERENCES clause is still there, the loan for member 99 is accepted again, and orphan_loans goes back to 1 — the schema looks identical in every respect and enforces nothing. Second, keep the pragma but change TODO 3's column to copies INTEGER CHECK (copies > 0), dropping the NOT NULL, and insert a loan with NULL copies: it is accepted, because the check evaluates to unknown rather than false, and bad_copies counts it.

Auditing Before You Constrain

Adding a constraint to a table that already holds data raises a question the schema cannot answer: are the rows already there allowed under the new rule? You find out by auditing first, and there is more than one way to write that audit.

PRAGMA foreign_keys = ON;
CREATE TABLE parents (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE kids (id INTEGER PRIMARY KEY, parent_id INTEGER NOT NULL REFERENCES parents(id));
INSERT INTO parents VALUES (1,'Ada'),(2,'Bo');
INSERT INTO kids VALUES (10,1),(11,1),(12,2);
SELECT COUNT(*) AS orphans FROM kids k LEFT JOIN parents p ON p.id = k.parent_id WHERE p.id IS NULL;
SELECT COUNT(*) AS orphans2 FROM kids k WHERE NOT EXISTS (SELECT 1 FROM parents p WHERE p.id = k.parent_id);
SELECT COUNT(*) AS orphans3 FROM kids WHERE parent_id NOT IN (SELECT id FROM parents);

Milestone: The Warehouse Integrity Rules

The capstone builds an e-commerce schema in which orders belong to customers and line items belong to orders. Every report it produces joins along those links, so the reports are trustworthy only to the extent that the links cannot go stale.

Key Takeaways

• PRIMARY KEY uniquely identifies each row. For an INTEGER PRIMARY KEY it enforces both UNIQUE and NOT NULL; for a non-integer key such as TEXT PRIMARY KEY, SQLite enforces uniqueness only and will accept two NULL keys, so write NOT NULL explicitly
• FOREIGN KEY links rows across tables — but only once PRAGMA foreign_keys = ON; has run on that connection. Enforcement is off by default on SQLite, every Run starts a fresh connection, and without the pragma a REFERENCES clause is documentation that checks nothing. ON DELETE CASCADE and ON DELETE SET NULL are equally inert without it, and the default action is NO ACTION, not RESTRICT
• UNIQUE prevents duplicate values in a column or combination of columns, but treats each NULL as distinct, so multiple NULL values are allowed
• NOT NULL forces a column to always have a value — and it is what makes a CHECK on the same column total, because a CHECK passes any row whose condition evaluates to NULL rather than to false
• CHECK enforces arbitrary business rules at the database level, such as valid ranges or allowed enum values, and needs no pragma
• DEFAULT provides a fallback value when a column is omitted from an insert. A clock-derived default such as date('now') is fine to store but should never be quoted in prose or asserted in a test, since its value changes daily
• A constraint that is declared is not the same as a constraint that is checked. The only reliable way to know which you have is to attempt the violation and see whether you are refused
• Money belongs in an INTEGER column of cents rather than DECIMAL(10,2), which this engine stores as a float

Pro Tip: Treat the database as the last line of defense, not the first. Your application should validate input before it ever reaches SQL — but constraints ensure that even a buggy migration script, a raw psql session, or a future integration cannot silently corrupt your data. Define constraints early and liberally; it is far easier to relax a constraint later than to clean up a table full of invalid rows.

Next Steps

With constraints securing your data, the next lesson closes the core path with query optimization — how to read execution plans, use indexes effectively, and restructure queries so your database does less work to return the same results.