Constraints Overview NOT NULL · UNIQUE · PK · FK · CHECK

Constraints — Introduction

Rules the database enforces on every row, no matter who writes the data. The six PostgreSQL constraint types, the difference between column-level and table-level, and how to add or drop them on a live table.

What constraints do

Constraints are rules that the database itself enforces about the data inside a table. They sit alongside the column definitions and make sure every row obeys them — no matter who's writing, from which application, with what code. Constraints are the most reliable way to enforce data quality, because they're checked at the storage level, not in some application layer that might have a bug or get bypassed.

The five everyday constraints in PostgreSQL — NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK — cover the vast majority of integrity rules you'll ever need. PostgreSQL also offers a sixth, the EXCLUSION constraint, for special-purpose use cases.

The six constraint types

Reference table

Constraint	What it enforces
NOT NULL	The column must have a value — NULL is not allowed.
UNIQUE	Every value in the column (or set of columns) is distinct across rows.
PRIMARY KEY	NOT NULL + UNIQUE — the row's permanent identifier; one per table.
FOREIGN KEY	Values must match an existing row in another table — cross-table integrity.
CHECK	A boolean expression that must be true for every row.
EXCLUSION	No two rows may match a defined "conflict" condition (e.g. overlapping ranges).

Column-level vs table-level

Most constraints can be written in two places:

Column-level — attached directly to a column definition. Simple and idiomatic for single-column constraints.
Table-level — declared after all the column definitions. Required when the constraint involves multiple columns.

SQL — column-level vs table-level

-- Column-level: each constraint sits next to its column
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    name         VARCHAR(100) NOT NULL,
    price        NUMERIC(10,2) CHECK (price >= 0)
);

-- Table-level: constraints listed at the end
CREATE TABLE orders (
    order_id     INT,
    customer_id  INT,
    qty          INT,
    CONSTRAINT orders_pk       PRIMARY KEY (order_id),
    CONSTRAINT orders_qty_chk  CHECK (qty > 0),
    CONSTRAINT orders_cust_fk  FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);

💡 Naming your constraints pays off later. When you let PostgreSQL auto-generate names (orders_qty_check, etc.), error messages and migrations get harder to follow. Use CONSTRAINT name_ explicitly for anything important.

Adding constraints later with ALTER TABLE

You don't have to declare every constraint at table-creation time. ALTER TABLE lets you add or drop them on a live table.

SQL

-- Add a CHECK to an existing table
ALTER TABLE products
    ADD CONSTRAINT price_positive CHECK (price >= 0);

-- Drop it again
ALTER TABLE products
    DROP CONSTRAINT price_positive;

Adding a constraint scans the existing rows and rejects the change if any row already violates it. So get your data in order first, then enforce.

Inspecting constraints

To see what's defined on a table, query the system catalog or use \d table_name in psql:

SQL

SELECT conname, contype, pg_get_constraintdef(oid)
FROM   pg_constraint
WHERE  conrelid = 'orders'::regclass;

Where to go next

NOT NULL — make a column required.
UNIQUE — guarantee no duplicates.
PRIMARY KEY — the row's identity.
FOREIGN KEY — link tables together.
CHECK — your own boolean rule.