Constraints
Enforce data integrity with primary keys, foreign keys, unique, check, and not-null.
What you'll learn
- Why constraints are the backbone of data integrity
- Primary key and foreign key constraints in depth
- UNIQUE constraints to prevent duplicate values
- CHECK constraints for custom validation rules
- NOT NULL and DEFAULT as constraints
Concept
Why Constraints Matter
Constraints are rules the database enforces on every insert and update. They are the backbone of data integrity — they make it impossible to store invalid data, regardless of which application or user tries. Without constraints, every application that touches the database must independently validate the same rules, and sooner or later one of them will skip a check and corrupt the data.
Primary Key Constraint
A primary key constraint combines NOT NULL and UNIQUE: every row must have a non-null, unique value in the key column(s). Each table can have only one primary key. It is usually an auto-incrementing integer or a UUID. The database creates an index on the primary key automatically, which makes lookups by ID fast.
Foreign Key Constraint
A foreign key constraint ensures that a value in one table references an existing row in another table. FOREIGN KEY (user_id) REFERENCES users(id) means you cannot insert an order with a user_id that does not exist. You can also specify what happens on delete: ON DELETE CASCADE (delete dependent rows), ON DELETE SET NULL, or ON DELETE RESTRICT (the default — block the delete).
UNIQUE Constraint
A UNIQUE constraint ensures no two rows share the same value in a column or combination of columns. Unlike a primary key, a table can have multiple UNIQUE constraints, and they allow NULL values (though the exact NULL behavior varies by database). UNIQUE is commonly used for email addresses, usernames, and SKU codes.
CHECK Constraint
A CHECK constraint enforces a custom boolean condition: CHECK (price >= 0), CHECK (status IN ('pending', 'shipped', 'delivered')), CHECK (end_date >= start_date). The condition is evaluated on every insert and update. CHECK constraints are powerful but should not contain subqueries or non-deterministic functions.
Example
-- Table with every major constraint type
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
status VARCHAR(20) NOT NULL DEFAULT 'pending'
CHECK (status IN ('pending', 'paid', 'shipped', 'delivered', 'cancelled')),
coupon_code VARCHAR(20) UNIQUE,
created_at TIMESTAMP NOT NULL DEFAULT NOW()
);
-- Add a composite unique constraint
ALTER TABLE order_items
ADD CONSTRAINT unique_product_per_order
UNIQUE (order_id, product_id);
-- Add a check constraint later
ALTER TABLE orders
ADD CONSTRAINT check_paid_has_total
CHECK (status != 'paid' OR total > 0);
A table with PK, FK with cascade delete, CHECK on price and status, UNIQUE on coupon, plus a composite UNIQUE and a cross-column CHECK added via ALTER.
Try it
- SQL Formatter
Format constraint definitions in CREATE TABLE.
- JSON Schema Validator
JSON Schema is the NoSQL equivalent of SQL constraints.
Common mistakes
The mistake
Forgetting ON DELETE behavior for foreign keys
The fix
By default, deleting a referenced row is blocked. Decide explicitly: CASCADE for dependent rows, SET NULL for optional links, RESTRICT to block.
The mistake
Validating business rules only in application code
The fix
Put fundamental integrity rules (price >= 0, status in allowed set) in CHECK constraints. The database is the last line of defense against bad data.
Practice Constraints
7 exercises
Look up unfamiliar terms
A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.
Build real projects
Apply what you learned by building guided projects with starter code and solutions.
Decode error messages
Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.