Skip to content

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

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

Practice Constraints

Look up unfamiliar terms

A beginner-friendly glossary explains jargon in plain English — variables, functions, DOM, Promise, and more.

View glossary

Build real projects

Apply what you learned by building guided projects with starter code and solutions.

View projects

Decode error messages

Plain-English explanations of common errors — what they mean, why they happen, and how to fix them.

View errors