What database constraints are commonly used, and what does each enforce?
Quick Answer
`NOT NULL` requires a value. `UNIQUE` disallows duplicate values (NULLs usually exempt). `PRIMARY KEY` combines `NOT NULL` + `UNIQUE` as the table's main identifier. `FOREIGN KEY` requires a value to exist in another table's key. `CHECK` enforces an arbitrary boolean expression per row. `DEFAULT` supplies a value when none is given. Together, these push data-validity rules into the database itself, rather than trusting every application layer to enforce them consistently.
Detailed Answer
CREATE TABLE products (
id SERIAL PRIMARY KEY, -- PK: unique + not null
sku VARCHAR(50) NOT NULL UNIQUE, -- required, no duplicates
price NUMERIC(10,2) NOT NULL CHECK (price >= 0), -- arbitrary boolean rule
category_id INT REFERENCES categories(id), -- FK: must exist in categories
created_at TIMESTAMP NOT NULL DEFAULT now(), -- default value if omitted
status VARCHAR(20) NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'discontinued', 'draft'))
);
NOT NULL
Rejects inserts/updates that omit a value for that column. The simplest and most impactful constraint — a huge share of real-world "why is this NULL when it shouldn't be" bugs are just missing NOT NULL constraints that should have been there from the start.
UNIQUE
Guarantees no two rows share the same value in that column (or combination of columns, for a composite UNIQUE constraint). Backed internally by a unique index. Most engines allow multiple NULLs in a UNIQUE column, since NULL <> NULL under three-valued logic (see the NULL question) — NULL isn't considered a duplicate of another NULL.
PRIMARY KEY
Shorthand for NOT NULL + UNIQUE, plus the semantic meaning of "this is the table's canonical row identifier" and (in most engines) the default target for foreign keys and often the physical clustering key.
FOREIGN KEY
Requires the column's value to match an existing value in the referenced table's primary/unique key, or be NULL if the column is nullable. Enforces relational integrity — you can't have an order pointing at a customer that doesn't exist.
CHECK
An arbitrary boolean expression evaluated per row on insert/update — the most flexible constraint, used for business rules a simple type/uniqueness check can't express (price >= 0, end_date > start_date, status IN (...)).
DEFAULT
Not a validity constraint per se, but supplies a value automatically when a column is omitted from an INSERT — commonly used for created_at timestamps, or a sensible default status/flag.
Why enforce these in the database rather than just in application code
Constraints are the last line of defense against bad data — they catch bugs from any write path (a new microservice, a one-off migration script, a manual psql fix) that an application-layer validation library only protects if every writer remembers to call it. They also let the query optimizer make stronger assumptions (e.g., a NOT NULL foreign key guarantees a matching row exists, which can simplify join planning).