PostgreSQL Constraints - PostgreSQL Tutorial

Constraints let the database enforce correctness, so bad data cannot get in even through a buggy application or a manual query.

SQL

CREATE TABLE books (
    id        BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    isbn      TEXT UNIQUE,
    title     TEXT NOT NULL,
    price     NUMERIC(10,2) NOT NULL CHECK (price >= 0),
    stock     INT NOT NULL DEFAULT 0 CHECK (stock >= 0),
    author_id BIGINT REFERENCES authors(id) ON DELETE SET NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

The main constraint types

Constraint	Guarantees
`PRIMARY KEY`	unique and not null; one per table
`UNIQUE`	no duplicate values
`NOT NULL`	a value is always present
`CHECK`	an arbitrary condition holds
`REFERENCES`	the value exists in another table
`DEFAULT`	value used when none is supplied

Foreign keys and referential actions

SQL

author_id BIGINT REFERENCES authors(id) ON DELETE CASCADE

Action	On deleting the parent
`NO ACTION` / `RESTRICT`	block the delete (default)
`CASCADE`	delete the children too
`SET NULL`	null out the reference
`SET DEFAULT`	set the column to its default

CASCADE is convenient and dangerous — deleting one author can silently remove thousands of books. Use it where children genuinely cannot exist alone (order items), and RESTRICT where they can.

Multi-column constraints

SQL

CREATE TABLE enrollments (
    student_id BIGINT REFERENCES students(id),
    course_id  BIGINT REFERENCES courses(id),
    PRIMARY KEY (student_id, course_id)     -- composite key
);

CREATE TABLE shelves (
    user_id BIGINT,
    name    TEXT,
    UNIQUE (user_id, name)     -- names unique per user, not globally
);

Named constraints

Naming them gives you readable error messages and lets you drop them later:

SQL

CREATE TABLE books (
    price NUMERIC(10,2) CONSTRAINT price_non_negative CHECK (price >= 0)
);

ALTER TABLE books DROP CONSTRAINT price_non_negative;

Adding constraints to an existing table

SQL

ALTER TABLE books ADD CONSTRAINT price_check CHECK (price >= 0);
ALTER TABLE books ALTER COLUMN title SET NOT NULL;
ALTER TABLE books ADD CONSTRAINT fk_author
    FOREIGN KEY (author_id) REFERENCES authors(id);

Adding a constraint validates every existing row, which locks the table on large datasets. Add it as NOT VALID first, then validate separately:

SQL

ALTER TABLE books ADD CONSTRAINT price_check CHECK (price >= 0) NOT VALID;
ALTER TABLE books VALIDATE CONSTRAINT price_check;   -- no heavy lock

NULL and UNIQUE

By default, multiple NULLs are allowed in a UNIQUE column — NULLs are never equal to each other. Postgres 15+ can change that:

SQL

UNIQUE NULLS NOT DISTINCT (email)