Lesson 10 intermediate

Constraints and Data Integrity

Let the database guarantee that data stays valid: NOT NULL, primary keys, unique and case-insensitive unique rules, check constraints, foreign keys with the right ON DELETE action, exclusion constraints for overlapping ranges, deferrable constraints, and how to add constraints to big tables without downtime.

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

ConstraintGuarantees
NOT NULLA column always has a value
PRIMARY KEYA unique, non-null identifier for each row
UNIQUENo two rows share a value (or combination of values)
CHECKEach row satisfies a boolean expression
FOREIGN KEYA value exists in another table
EXCLUDENo 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:

ActionEffect when the parent row is deleted
NO ACTION / RESTRICT (default)The delete fails while child rows exist
CASCADEChild rows are deleted too
SET NULLThe child column is set to NULL
SET DEFAULTThe 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

TaskSQL
Unique combinationALTER TABLE t ADD CONSTRAINT k UNIQUE (a, b);
Case-insensitive uniqueCREATE UNIQUE INDEX ON t (lower(col));
Row ruleADD CONSTRAINT c CHECK (expr);
Foreign keyREFERENCES parent (id) ON DELETE CASCADE / RESTRICT / SET NULL
No overlapping rangesEXCLUDE USING gist (k WITH =, r WITH &&)
Check at commitDEFERRABLE + SET CONSTRAINTS ... DEFERRED
Add without a long lock... NOT VALID; then VALIDATE CONSTRAINT
List constraintspg_constraint or \d table
RemoveALTER 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?

  1. UNIQUE (email)
  2. CHECK (email = lower(email))
  3. CREATE UNIQUE INDEX ON users (lower(email))
  4. email citext NOT NULL
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?

  1. Add it during peak hours so problems show up quickly
  2. Add it with NOT VALID, then run VALIDATE CONSTRAINT separately
  3. Copy the table, add the key to the copy and rename it
  4. Add a CHECK constraint instead
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?

  1. ON DELETE CASCADE
  2. ON DELETE RESTRICT
  3. ON DELETE NO ACTION
  4. ON DELETE SET NULL, with user_id allowed to be NULL
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 DELETE action on purpose, and index the referencing column.
  • Exclusion constraints prevent overlapping ranges; deferrable constraints are checked at commit.
  • On big tables, add constraints NOT VALID and 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.

↑ ↓ navigate Enter open Esc close