TopolearnConstraints stop bad data at the door, no matter which application or script writes it.
Constraints turn assumptions into guarantees. The database checks them on every insert and update and rejects the statement if one fails.
NOT NULL: the column must have a value.UNIQUE: no two rows share the value (or combination, forUNIQUE (a, b)). It is enforced by a unique index.CHECK (expression): every row must satisfy the condition, such asprice >= 0orstatus IN ('pending', 'shipped', 'cancelled'). A check may compare several columns of the same row, but cannot look at other rows or tables.DEFAULT: not a restriction, but declared alongside: the value used when an insert omits the column.
Two NULL subtleties matter. First, UNIQUE treats NULLs as distinct, so many rows may have a NULL email. PostgreSQL 15 added UNIQUE NULLS NOT DISTINCT for the other behaviour; SQL Server, unusually, allows only one NULL. Second, a CHECK passes when its expression is NULL. CHECK (price > 0) accepts a NULL price; add NOT NULL if the value is required.
Name your constraints (CONSTRAINT price_positive CHECK (...)). The name appears in error messages, which makes failures readable, and you need it to drop the constraint later.
Adding a constraint to a big existing table must validate every row while holding a lock. PostgreSQL lets you split that: ADD CONSTRAINT ... NOT VALID enforces the rule for new writes immediately, and a later VALIDATE CONSTRAINT checks old rows with a much weaker lock.
Dialect note: MySQL parsed but silently ignored CHECK constraints until 8.0.16. Do not assume an old MySQL schema enforces what it declares.
Example
CREATE TABLE products (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
sku text NOT NULL,
name text NOT NULL,
category text NOT NULL DEFAULT 'hardware',
price numeric(10,2) NOT NULL,
CONSTRAINT products_sku_key UNIQUE (sku),
CONSTRAINT price_non_negative CHECK (price >= 0),
CONSTRAINT category_known
CHECK (category IN ('hardware', 'digital'))
);
INSERT INTO products (sku, name, price) VALUES ('KB-1', 'Keyboard', -5);
-- ERROR: new row for relation "products" violates check constraint
-- "price_non_negative"
-- Add a rule to a large table without a long lock
ALTER TABLE orders ADD CONSTRAINT total_non_negative
CHECK (total >= 0) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT total_non_negative;Remember
- Constraints are enforced for every writer, unlike application checks
- UNIQUE allows multiple NULLs; CHECK passes when the expression is NULL
- Name constraints so errors are readable
- NOT VALID + VALIDATE adds a constraint to a big table safely
Watch out for
- Writing CHECK (price > 0) and assuming it also forbids a NULL price.