Constraints

A constraint is a rule the database enforces. You declare it once on the table, and Postgres rejects any statement that would break it.

CREATE TABLE accounts (
  id      int PRIMARY KEY,
  email   text NOT NULL UNIQUE,
  balance numeric NOT NULL CHECK (balance >= 0)
);

• NOT NULL: a value is required.
• PRIMARY KEY: UNIQUE plus NOT NULL. A table has at most one.
• UNIQUE: no duplicates. Several NULLs are allowed, because no two NULLs are equal.
• CHECK: any boolean expression on the row. INSERT INTO accounts VALUES (1, 'x@example.com', -25) fails with SQLSTATE 23514.
• FOREIGN KEY: the value must exist in another table.

Each failure has its own SQLSTATE: 23502 not null, 23505 unique, 23514 check, 23503 foreign key.

Since PostgreSQL 18 a NOT NULL constraint is a named row in pg_constraint, like the others.