Constraints

A constraint is a rule attached to a table that PostgreSQL enforces on every INSERT, UPDATE, and sometimes DELETE — no exceptions, no matter which application, script, or person is doing the writing. Primary keys and foreign keys, covered on their own pages, are two specific kinds of constraint. This page rounds out the full picture: every constraint type PostgreSQL supports, and why enforcing rules at the database level matters even when the application layer already validates the same thing.

The constraint types

Constraint	Enforces
PRIMARY KEY	Column(s) uniquely identify each row; implies NOT NULL + UNIQUE
FOREIGN KEY	A column's value must match a row in another table (referential integrity)
UNIQUE	No two rows can have the same value in the column(s)
NOT NULL	The column can never be NULL
CHECK	A boolean expression must hold true for every row
EXCLUSION	No two rows may satisfy a given operator comparison at the same time (e.g. no overlapping ranges)

The everyday constraint types in one table

SQL

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  sku        TEXT UNIQUE NOT NULL,
  name       TEXT NOT NULL,
  price      NUMERIC(10, 2) NOT NULL CHECK (price >= 0),
  category_id INTEGER REFERENCES categories (category_id)
);

EXCLUSION constraints — a distinctive PostgreSQL feature

The first five constraint types have equivalents in essentially every relational database. EXCLUSION constraints are different: they are a genuinely PostgreSQL feature that generalizes UNIQUE to arbitrary operators, not just equality. A UNIQUE constraint says "no two rows can be equal on this column." An EXCLUDE constraint says "no two rows can satisfy this operator" for any operator with an appropriate index support — most commonly used to forbid overlapping time or numeric ranges.

Preventing overlapping room reservations

SQL

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE reservations (
  reservation_id SERIAL PRIMARY KEY,
  room_id        INTEGER NOT NULL,
  during         TSRANGE NOT NULL,
  EXCLUDE USING GIST (room_id WITH =, during WITH &&)
);

This says: for a given room_id, no two reservations may have overlapping during ranges. The Range Types page walks through this exact reservation-overlap example in full, including why it needs a GiST index to work.

Naming constraints explicitly

Every constraint gets a name whether you provide one or not — if you don't, PostgreSQL generates one like products_price_check. The trouble with an auto-generated name is that it shows up verbatim in error messages, and products_price_check is far less useful to a developer or a support engineer than a name that states the actual rule.

A named CHECK constraint gives a clearer error

SQL

CREATE TABLE products (
  product_id SERIAL PRIMARY KEY,
  price      NUMERIC(10, 2) NOT NULL,
  CONSTRAINT price_must_be_non_negative CHECK (price >= 0)
);

INSERT INTO products (price) VALUES (-5);

ERROR:  new row for relation "products" violates check constraint "price_must_be_non_negative"
DETAIL:  Failing row contains (1, -5.00).

price_must_be_non_negative reads as a sentence; anyone debugging the failure immediately knows what rule was broken without having to go look up the table definition.

Why enforce rules in the database at all

Application code can validate input too, and often should — for better error messages shown to a user, for instance. But application-level validation only protects the paths that go through that particular application. A one-off migration script, an admin running a manual UPDATE, a second service that gets added later and talks to the same database, a bulk import — none of those go through the application's validation code, and any one of them can silently corrupt the data if the database itself doesn't also enforce the rule.

A constraint declared in the schema is the last line of defense: no matter what wrote the row, or forgot to check something, the constraint holds. It is also self-documenting — anyone reading the CREATE TABLE statement sees the business rules the data must satisfy, rather than having to trace through application code scattered across the codebase.

Constraints are usually cheap

NOT NULL and simple CHECK constraints add negligible overhead to writes. Even so, if you are worried about the cost of validating a constraint against a huge amount of existing data when adding it to a table, PostgreSQL supports adding a CHECK constraint as NOT VALID first, then validating it separately without holding a long lock — a pattern the Alter Table page covers.

PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, and CHECK cover the everyday cases.
EXCLUSION constraints generalize UNIQUE to arbitrary operators — most notably, preventing overlapping ranges.
Name constraints explicitly (CONSTRAINT name ...) so error messages describe the rule, not a generated identifier.
Database-level constraints are the last line of defense against writes that bypass application validation entirely.