Application code changes, has bugs and is not the only thing writing to your database: scripts, migrations, admin tools and other services do too. Constraints are rules the database enforces on every write, whoever makes it. They are the cheapest bug prevention you will ever get. This lesson tightens Orbit’s tables.
The constraint types
| Constraint | Guarantees |
|---|---|
NOT NULL | A column always has a value |
PRIMARY KEY | A unique, non-null identifier for each row |
UNIQUE | No two rows share a value (or combination of values) |
CHECK | Each row satisfies a boolean expression |
FOREIGN KEY | A value exists in another table |
EXCLUDE | No two rows conflict according to an operator, such as overlapping ranges |
NOT NULL and primary keys
You declared these in lesson 4. See them work:
INSERT INTO notes (user_id) VALUES (1);
ERROR: null value in column "title" of relation "notes" violates not-null constraint
Make columns NOT NULL unless “unknown” is a real, meaningful state. Fewer NULLs mean fewer three-valued-logic surprises (lesson 7).
UNIQUE
The UNIQUE on users.email rejects a second account with the same address:
INSERT INTO users (email, name) VALUES ('ana@orbit.dev', 'Another Ana');
ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=(ana@orbit.dev) already exists.
But ANA@orbit.dev would be accepted, because text comparison is case-sensitive. A unique index on an expression closes that gap:
CREATE UNIQUE INDEX users_email_lower_key ON users (lower(email));
INSERT INTO users (email, name) VALUES ('ANA@orbit.dev', 'Shouting Ana');
CREATE INDEX
ERROR: duplicate key value violates unique constraint "users_email_lower_key"
DETAIL: Key (lower(email))=(ana@orbit.dev) already exists.
Uniqueness can span several columns. Orbit should not let one user have two notes with the same title, while different users may:
ALTER TABLE notes ADD CONSTRAINT notes_user_title_key UNIQUE (user_id, title);
By default NULLs are never equal to each other, so a unique column may contain many NULLs. Since PostgreSQL 15, UNIQUE NULLS NOT DISTINCT treats them as equal when that is what you need.
CHECK
A CHECK constraint is any boolean expression on the row. Lesson 5 limited title length; also forbid titles made only of spaces:
ALTER TABLE notes ADD CONSTRAINT notes_title_not_blank CHECK (btrim(title) <> '');
INSERT INTO notes (user_id, title) VALUES (1, ' ');
ALTER TABLE
ERROR: new row for relation "notes" violates check constraint "notes_title_not_blank"
(A DETAIL line with the failing row follows.) Checks can compare columns too, for example CHECK (ends_at > starts_at). They cannot look at other rows or other tables; that is what unique, foreign key and exclusion constraints are for.
Tip: Name your constraints. The name appears in error messages, and applications can map notes_title_not_blank to a friendly “Please enter a title”.
Foreign keys
notes.user_id REFERENCES users (id) guarantees that every note belongs to an existing user:
INSERT INTO notes (user_id, title) VALUES (99, 'Ghost note');
ERROR: insert or update on table "notes" violates foreign key constraint "notes_user_id_fkey"
DETAIL: Key (user_id)=(99) is not present in table "users".
The ON DELETE action decides what happens to notes when their user is deleted:
| Action | Effect when the parent row is deleted |
|---|---|
NO ACTION / RESTRICT (default) | The delete fails while child rows exist |
CASCADE | Child rows are deleted too |
SET NULL | The child column is set to NULL |
SET DEFAULT | The child column is set to its default |
Orbit uses CASCADE: deleting an account deletes its notes. Try it safely inside a transaction:
BEGIN;
DELETE FROM users WHERE id = 2;
SELECT count(*) FROM notes;
ROLLBACK;
BEGIN
DELETE 1
count
-------
3
(1 row)
ROLLBACK
Ben’s two notes disappeared along with Ben, and ROLLBACK brought all three rows back. Choose CASCADE deliberately: for invoices or audit records, RESTRICT is usually the safer default.
Remember from lesson 8 that Postgres does not index the referencing column. Without an index on notes.user_id, deleting a user scans the whole notes table to find the children. Lesson 11 adds the index.
Exclusion constraints
Some rules involve ranges. Suppose Orbit adds focus sessions, and one user must never have two that overlap in time. A unique constraint cannot express “overlaps”; an exclusion constraint can:
CREATE EXTENSION IF NOT EXISTS btree_gist;
CREATE TABLE focus_sessions (
user_id bigint NOT NULL REFERENCES users (id) ON DELETE CASCADE,
during tstzrange NOT NULL,
EXCLUDE USING gist (user_id WITH =, during WITH &&)
);
It reads: no two rows may have equal user_id and overlapping (&&) during ranges. The btree_gist extension lets the GiST index handle the plain = comparison on user_id. PostgreSQL 18 adds a shorter form for keys over time, PRIMARY KEY (user_id, during WITHOUT OVERLAPS), which lesson 19 shows.
Deferrable constraints
Constraints are checked after each statement. Occasionally you need a moment of inconsistency inside a transaction, for example swapping the positions of two items that have a unique position. Declare the constraint DEFERRABLE and defer it:
ALTER TABLE playlist_items ADD CONSTRAINT playlist_position_key
UNIQUE (playlist_id, position) DEFERRABLE INITIALLY IMMEDIATE;
BEGIN;
SET CONSTRAINTS playlist_position_key DEFERRED;
UPDATE playlist_items SET position = 2 WHERE id = 10;
UPDATE playlist_items SET position = 1 WHERE id = 11;
COMMIT; -- checked here
Adding constraints to big tables safely
Adding a foreign key or check constraint normally scans the whole table to validate existing rows, while holding locks that block writes. On a large, busy table, split it in two steps:
ALTER TABLE notes ADD CONSTRAINT notes_body_length CHECK (char_length(body) <= 100000) NOT VALID;
ALTER TABLE notes VALIDATE CONSTRAINT notes_body_length;
NOT VALID makes the constraint apply to new and updated rows immediately, without checking old ones. VALIDATE CONSTRAINT then checks old rows while allowing normal reads and writes. For unique constraints, build the index first with CREATE UNIQUE INDEX CONCURRENTLY (lesson 11), then attach it with ALTER TABLE ... ADD CONSTRAINT ... UNIQUE USING INDEX ....
Listing a table’s constraints
SELECT conname, contype, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'notes'::regclass AND contype <> 'n'
ORDER BY conname;
conname | contype | definition
-----------------------+---------+--------------------------------------------------------------
notes_body_length | c | CHECK ((char_length(body) <= 100000))
notes_pkey | p | PRIMARY KEY (id)
notes_title_length | c | CHECK ((char_length(title) <= 200))
notes_title_not_blank | c | CHECK ((btrim(title) <> ''::text))
notes_user_id_fkey | f | FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
notes_user_title_key | u | UNIQUE (user_id, title)
(6 rows)
contype is p for primary key, f foreign key, u unique, c check and x exclusion. Since PostgreSQL 18, NOT NULL constraints are also stored here, with type n; the query filters them out to keep the list short. To remove a constraint, use ALTER TABLE notes DROP CONSTRAINT name;.
Quick reference
| Task | SQL |
|---|---|
| Unique combination | ALTER TABLE t ADD CONSTRAINT k UNIQUE (a, b); |
| Case-insensitive unique | CREATE UNIQUE INDEX ON t (lower(col)); |
| Row rule | ADD CONSTRAINT c CHECK (expr); |
| Foreign key | REFERENCES parent (id) ON DELETE CASCADE / RESTRICT / SET NULL |
| No overlapping ranges | EXCLUDE USING gist (k WITH =, r WITH &&) |
| Check at commit | DEFERRABLE + SET CONSTRAINTS ... DEFERRED |
| Add without a long lock | ... NOT VALID; then VALIDATE CONSTRAINT |
| List constraints | pg_constraint or \d table |
| Remove | ALTER TABLE t DROP CONSTRAINT name; |
Exercise
Orbit must reject a second account for Ana@Orbit.dev when ana@orbit.dev exists. Which rule does it?
Show answer
Answer: C. A unique index on lower(email) compares addresses case-insensitively. Plain UNIQUE treats the two spellings as different values. A citext column with a unique constraint would also work, but it changes the column type.
Exercise
You must add a foreign key to a 300 GB table that receives writes all day. What is the safest approach?
Show answer
Answer: B. NOT VALID adds the constraint for new rows instantly. VALIDATE then scans existing rows with a lock that does not block normal reads and writes.
Exercise
A user is deleted and their notes must stay, but without an author. Which foreign key action fits?
Show answer
Answer: D. SET NULL keeps the child rows and clears the reference. The column must not be NOT NULL for this to work.
Key takeaways
- Constraints protect data from every writer, not just your application.
- Use expression indexes such as
lower(email)for case-insensitive uniqueness. - Choose the foreign key
ON DELETEaction on purpose, and index the referencing column. - Exclusion constraints prevent overlapping ranges; deferrable constraints are checked at commit.
- On big tables, add constraints
NOT VALIDand validate them later.
FAQ
Should validation live in the database or in the application?
Both. The application gives fast, friendly feedback; the database guarantees the rules hold no matter who writes. Keep the essential invariants (uniqueness, references, required fields, valid ranges) in the database.
Do constraints slow down writes?
A little: unique and foreign keys need index lookups, and checks evaluate an expression. The cost is small compared with cleaning up corrupted data later.
Can a CHECK constraint call my own function?
Yes, if the function is immutable and only looks at the row’s values. Do not use it to query other tables: such checks are not re-evaluated when the other table changes, so the rule silently stops holding.
How do I find rows that would break a constraint before adding it?
Run the opposite query first, for example SELECT id, title FROM notes WHERE btrim(title) = '';, fix those rows, then add the constraint.
Why do some foreign keys appear as triggers in \d output?
Foreign keys are implemented with internal system triggers that run the checks. They are managed for you and disappear when you drop the constraint.