Constraints

Rules the database enforces on every write: NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK.

Syntax

col type NOT NULL
col type UNIQUE                      [NULLS NOT DISTINCT]
PRIMARY KEY (col [, col])
FOREIGN KEY (col) REFERENCES parent (col) [ON DELETE CASCADE | SET NULL | RESTRICT]
CHECK (condition)
CONSTRAINT name ...

NOT NULL: the column must have a value.
UNIQUE: no two rows share the value(s). NULLs are considered distinct, so several NULLs are allowed — unless NULLS NOT DISTINCT (PostgreSQL 15+). Creates a unique index.
PRIMARY KEY = UNIQUE + NOT NULL; one per table; identifies each row.
FOREIGN KEY: values must exist in the referenced (unique) column of the parent, or be NULL. ON DELETE CASCADE deletes children with the parent; SET NULL clears the reference; the default (NO ACTION) blocks the delete. PostgreSQL does not index the referencing column automatically.
CHECK: a row-level condition. It passes when the condition is TRUE or NULL — add NOT NULL if needed.
Name constraints (CONSTRAINT price_positive CHECK (price > 0)) so error messages are readable.
Violations raise errors such as duplicate key value violates unique constraint and abort the statement.

Examples

See the constraints of a table shop

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

contype: p = primary key, f = foreign key, c = check, u = unique.

A CHECK violation, caught starter

DO $$
BEGIN
  INSERT INTO products (product_id, product_name, category, price, stock)
  VALUES (200, 'Broken', 'Test', -5, 1);
EXCEPTION WHEN check_violation THEN
  RAISE NOTICE 'rejected: %', SQLERRM;
END $$;
SELECT count(*) AS products_with_negative_price FROM products WHERE price < 0;

The negative price is rejected by the CHECK constraint; no row is stored.

Adding a named constraint (modifies schema) starter

ALTER TABLE customers
  ADD CONSTRAINT customers_email_format CHECK (email LIKE '%@%');

SELECT conname, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'customers'::regclass AND contype = 'c';

Existing rows are checked when the constraint is added; NULL emails pass because CHECK accepts NULL.

Pitfalls

Expecting CHECK (x > 0) to reject NULL (it does not).
Expecting UNIQUE to reject a second NULL (it does not, unless NULLS NOT DISTINCT).
Forgetting to index foreign-key columns used in joins and deletes.
ON DELETE CASCADE deleting far more than expected.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

Snowflake: PRIMARY KEY, UNIQUE and FOREIGN KEY are accepted but not enforced (only NOT NULL is) on standard tables.
BigQuery: Primary and foreign keys are informational only (NOT ENFORCED).
SQL Server: A UNIQUE constraint allows only one NULL; use a filtered unique index for more.
MySQL: CHECK constraints are enforced since 8.0.16 (parsed but ignored before).