SQL Constraints Table
| Constraint | What it enforces | Example |
|---|---|---|
| PRIMARY KEY | Uniquely identifies each row; NOT NULL + UNIQUE automatically | id SERIAL PRIMARY KEY |
| FOREIGN KEY | Value must exist in the referenced table\u2019s column | user_id INT REFERENCES users(id) |
| UNIQUE | No duplicate values in the column (NULLs allowed in PostgreSQL) | email VARCHAR(255) UNIQUE |
| CHECK | Boolean expression must be true for every row | price DECIMAL CHECK (price >= 0) |
| NOT NULL | Column must have a value (no NULL) | name TEXT NOT NULL |
| DEFAULT | Value used when none is provided | created_at TIMESTAMPTZ DEFAULT now() |
| EXCLUDE | Rows must not conflict using the specified operator | EXCLUDE USING gist (room WITH =, during WITH &&) |
SQL constraints are the database’s immune system: they reject bad data at the door, before any application code runs. This table covers all 7 constraint types verbatim from the PostgreSQL documentation - from PRIMARY KEY (which combines NOT NULL and UNIQUE) to the powerful but obscure EXCLUDE constraint.
The cheapest data quality investment: a NOT NULL on the name column and a CHECK on the price column have prevented more data corruption than any application-layer validation, because they run on every INSERT and UPDATE regardless of which code path or ORM produced the query.
How to use
- Filter by constraint name or keyword - primary, foreign, unique, check - to find what you need.
- Click any constraint to copy the name for documentation or CREATE TABLE.
- Read the example column for the exact SQL syntax in PostgreSQL.
Frequently asked questions
What is the NULL trap with UNIQUE constraints?
In PostgreSQL, NULL is not equal to NULL - so multiple rows can have NULL in a UNIQUE column without violating the constraint. This is correct per the SQL standard but surprises developers who expect UNIQUE to prevent duplicates of empty values. Use NOT NULL with UNIQUE (or a PRIMARY KEY) if you need exactly one non-null value per row.
Why did my LEFT JOIN become an INNER JOIN?
If you put a condition on the right table in the WHERE clause (instead of ON), you eliminate the NULL rows that LEFT JOIN preserves. For example, LEFT JOIN orders ON users.id = orders.user_id WHERE orders.status = 'active' drops users with no active orders because orders.status is NULL for them. Move the condition to the ON clause to keep unmatched left rows.
What is the EXCLUDE constraint?
PostgreSQL-specific: it ensures that specified columns have no conflicts using the given operator. The classic example is booking systems: EXCLUDE USING gist (room_id WITH =, during WITH &&) prevents double-booking the same room for overlapping time ranges. It requires the btree_gist extension.
Can I add a constraint to an existing table with data?
Yes - but the constraint is validated against existing rows. If any row violates the constraint, the ALTER TABLE fails. Use NOT VALID to add the constraint without checking existing rows, then VALIDATE CONSTRAINT separately to check without locking the table for writes.