Skip to content
Tutorial Reference

SQL Constraints for Ensuring Data Integrity

Learn how to use SQL constraints like PRIMARY KEY, NOT NULL, UNIQUE, FOREIGN KEY, CHECK, and DEFAULT to enforce data integrity, prevent invalid entries, and maintain reliable database relationships.

Databases are only as valuable as the data they contain. If a customer record can exist without a name, if an order can reference a customer that does not exist, or if two users can register with the same email address, your application is built on unstable ground. Bugs multiply, reports become unreliable, and debugging turns into detective work.

SQL constraints are rules you attach to columns and tables that the database engine enforces automatically, every time data is inserted, updated, or deleted. They act as a permanent safety net that catches invalid data before it enters the system, regardless of whether the data comes from your application, a manual SQL query, a data migration script, or a third-party integration.

This guide covers every major constraint type in depth, with practical examples, common mistakes, and the naming conventions that keep your schema professional and maintainable.

Why Constraints Matter

Consider a simple orders table without any constraints:

CREATE TABLE orders (
    order_id      INT,
    customer_id   INT,
    order_date    DATE,
    status        VARCHAR(20),
    total_amount  DECIMAL(10,2)
);

This table will accept absolutely anything:

-- Duplicate order IDs? Sure.
INSERT INTO orders VALUES (1, 10, '2024-06-15', 'pending', 99.99);
INSERT INTO orders VALUES (1, 20, '2024-06-16', 'shipped', 50.00);

-- NULL order ID? No problem.
INSERT INTO orders VALUES (NULL, NULL, NULL, NULL, NULL);

-- Negative total? Why not.
INSERT INTO orders VALUES (2, 10, '2024-06-15', 'pending', -500.00);

-- A customer that doesn't exist? Go ahead.
INSERT INTO orders VALUES (3, 99999, '2024-06-15', 'pending', 75.00);

-- Status that makes no sense? Accepted.
INSERT INTO orders VALUES (4, 10, '2024-06-15', 'banana', 100.00);

Every one of these inserts succeeds. The database stores all of it without complaint. Your application now contains duplicate IDs, orphaned references, impossible values, and nonsensical statuses. Constraints prevent every single one of these problems.

The Sample Schema

All examples in this guide build on these tables:

CREATE TABLE departments (
    department_id    INT,
    department_name  VARCHAR(100),
    budget           DECIMAL(12,2)
);

CREATE TABLE employees (
    employee_id      INT,
    first_name       VARCHAR(50),
    last_name        VARCHAR(50),
    email            VARCHAR(100),
    department_id    INT,
    salary           DECIMAL(10,2),
    hire_date        DATE
);

CREATE TABLE projects (
    project_id       INT,
    project_name     VARCHAR(100),
    start_date       DATE,
    end_date         DATE,
    status           VARCHAR(20)
);

We will progressively add constraints to these tables throughout the guide, transforming them from wide-open structures into robust, self-protecting schemas.

PRIMARY KEY

A primary key uniquely identifies every row in a table. It guarantees two things simultaneously: the value is unique across all rows, and it is never NULL. Every table should have a primary key.

Syntax

-- Inline (single column)
CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(100)
);

-- Table-level (also works for single columns, required for composite keys)
CREATE TABLE departments (
    department_id   INT,
    department_name VARCHAR(100),
    PRIMARY KEY (department_id)
);

Example

CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(100)
);

-- Works: unique, non-null ID
INSERT INTO departments VALUES (1, 'Engineering');
INSERT INTO departments VALUES (2, 'Marketing');

-- Fails: duplicate primary key
INSERT INTO departments VALUES (1, 'Sales');
ERROR: duplicate key value violates unique constraint "departments_pkey"
DETAIL: Key (department_id)=(1) already exists.
-- Fails: NULL primary key
INSERT INTO departments VALUES (NULL, 'Sales');
ERROR: null value in column "department_id" violates not-null constraint

Composite Primary Keys

Some tables need multiple columns together to uniquely identify a row. This is common in junction tables (also called bridge tables or associative tables) that represent many-to-many relationships.

CREATE TABLE project_assignments (
    employee_id   INT,
    project_id    INT,
    role          VARCHAR(50),
    assigned_date DATE,
    PRIMARY KEY (employee_id, project_id)
);

The combination of employee_id and project_id must be unique. An employee can be assigned to multiple projects, and a project can have multiple employees, but the same employee cannot be assigned to the same project twice.

-- Works: different combinations
INSERT INTO project_assignments VALUES (101, 1, 'Developer', '2024-01-15');
INSERT INTO project_assignments VALUES (101, 2, 'Lead',      '2024-02-01');
INSERT INTO project_assignments VALUES (102, 1, 'Tester',    '2024-01-20');

-- Fails: employee 101 already assigned to project 1
INSERT INTO project_assignments VALUES (101, 1, 'Manager', '2024-03-01');
ERROR: duplicate key value violates unique constraint "project_assignments_pkey"
DETAIL: Key (employee_id, project_id)=(101, 1) already exists.

Auto-Generated Primary Keys

In practice, primary keys are usually auto-generated rather than manually assigned. The syntax varies by database:

-- PostgreSQL
CREATE TABLE employees (
    employee_id  INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name   VARCHAR(50) NOT NULL
);

-- MySQL
CREATE TABLE employees (
    employee_id  INT AUTO_INCREMENT PRIMARY KEY,
    first_name   VARCHAR(50) NOT NULL
);

-- SQL Server
CREATE TABLE employees (
    employee_id  INT IDENTITY(1,1) PRIMARY KEY,
    first_name   VARCHAR(50) NOT NULL
);

-- SQLite
CREATE TABLE employees (
    employee_id  INTEGER PRIMARY KEY AUTOINCREMENT,
    first_name   TEXT NOT NULL
);

With auto-generated keys, you omit the primary key column from your INSERT and let the database assign the next available value:

INSERT INTO employees (first_name) VALUES ('Alice');
-- employee_id = 1 (auto-assigned)

INSERT INTO employees (first_name) VALUES ('Bob');
-- employee_id = 2 (auto-assigned)

NOT NULL

The NOT NULL constraint prevents a column from storing NULL values. Any insert or update that would result in a NULL value for that column is rejected.

Syntax

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    last_name     VARCHAR(50) NOT NULL,
    email         VARCHAR(100),             -- allows NULL
    salary        DECIMAL(10,2) NOT NULL
);

Example

-- Works: all NOT NULL columns have values
INSERT INTO employees (employee_id, first_name, last_name, email, salary)
VALUES (101, 'Alice', 'Martin', 'alice@example.com', 85000);

-- Works: email is nullable, so NULL is fine there
INSERT INTO employees (employee_id, first_name, last_name, email, salary)
VALUES (102, 'Bob', 'Jones', NULL, 72000);

-- Fails: first_name is NOT NULL
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (103, NULL, 'Smith', 68000);
ERROR: null value in column "first_name" violates not-null constraint

When to Use NOT NULL

The decision is straightforward: if a row is meaningless without a value in this column, mark it NOT NULL.

Column Should be NOT NULL? Reasoning
first_name Yes A person record without a name is useless
email Depends Required for login? NOT NULL. Optional contact info? Nullable
phone Usually no Many records won't have a phone number
order_date Yes Every order happens on a specific date
total_amount Depends Might be calculated later; nullable until then
created_at Yes (with DEFAULT) Should always be recorded

NOT NULL on UPDATE

The constraint also applies to updates:

-- Fails: cannot set a NOT NULL column to NULL
UPDATE employees SET first_name = NULL WHERE employee_id = 101;
ERROR: null value in column "first_name" violates not-null constraint

UNIQUE

The UNIQUE constraint ensures that no two rows in the table have the same value in the specified column (or combination of columns). Unlike PRIMARY KEY, a table can have multiple UNIQUE constraints, and UNIQUE columns generally allow NULL values.

Syntax

-- Inline (single column)
CREATE TABLE employees (
    employee_id  INT PRIMARY KEY,
    email        VARCHAR(100) UNIQUE
);

-- Table-level (required for multi-column unique constraints)
CREATE TABLE employees (
    employee_id  INT PRIMARY KEY,
    first_name   VARCHAR(50),
    last_name    VARCHAR(50),
    email        VARCHAR(100) UNIQUE,
    UNIQUE (first_name, last_name)       -- composite unique constraint
);

Example

CREATE TABLE users (
    user_id    INT PRIMARY KEY,
    username   VARCHAR(50)  UNIQUE NOT NULL,
    email      VARCHAR(100) UNIQUE NOT NULL,
    full_name  VARCHAR(100)
);

-- Works
INSERT INTO users VALUES (1, 'alice', 'alice@example.com', 'Alice Martin');
INSERT INTO users VALUES (2, 'bob',   'bob@example.com',   'Bob Jones');

-- Fails: duplicate username
INSERT INTO users VALUES (3, 'alice', 'alice2@example.com', 'Alice Smith');
ERROR: duplicate key value violates unique constraint "users_username_key"
DETAIL: Key (username)=(alice) already exists.
-- Fails: duplicate email
INSERT INTO users VALUES (3, 'carol', 'alice@example.com', 'Carol Davis');
ERROR: duplicate key value violates unique constraint "users_email_key"
DETAIL: Key (email)=(alice@example.com) already exists.

Multi-Column UNIQUE Constraints

A composite unique constraint ensures the combination of values is unique, not each column individually:

CREATE TABLE course_enrollments (
    enrollment_id  INT PRIMARY KEY,
    student_id     INT NOT NULL,
    course_id      INT NOT NULL,
    semester       VARCHAR(10) NOT NULL,
    grade          CHAR(2),
    UNIQUE (student_id, course_id, semester)
);

-- Works: different combinations
INSERT INTO course_enrollments VALUES (1, 100, 201, '2024-Fall', NULL);
INSERT INTO course_enrollments VALUES (2, 100, 202, '2024-Fall', NULL);
INSERT INTO course_enrollments VALUES (3, 100, 201, '2025-Spring', NULL);

-- Fails: student 100 already enrolled in course 201 for 2024-Fall
INSERT INTO course_enrollments VALUES (4, 100, 201, '2024-Fall', NULL);
ERROR: duplicate key value violates unique constraint
DETAIL: Key (student_id, course_id, semester)=(100, 201, 2024-Fall) already exists.

UNIQUE vs PRIMARY KEY

Feature PRIMARY KEY UNIQUE
Uniqueness enforced Yes Yes
Allows NULL No Yes (in most databases)
Number per table Exactly one Unlimited
Creates index automatically Yes Yes
Semantic meaning "This is THE identifier" "This must be distinct"

NULLs in UNIQUE Columns

The behavior of NULL in UNIQUE columns varies by database:

CREATE TABLE contacts (
    contact_id  INT PRIMARY KEY,
    phone       VARCHAR(20) UNIQUE    -- allows NULL
);

INSERT INTO contacts VALUES (1, '555-0100');
INSERT INTO contacts VALUES (2, NULL);
INSERT INTO contacts VALUES (3, NULL);   -- Two NULLs: is this allowed?
Database Multiple NULLs in UNIQUE column?
PostgreSQL Yes (NULLs are considered distinct)
MySQL Yes
SQLite Yes
SQL Server No (only one NULL allowed by default)
Oracle Yes (NULLs are not indexed)

FOREIGN KEY

A foreign key creates a link between two tables by requiring that values in one column must match existing values in another table's column. This enforces referential integrity, ensuring that relationships between tables remain valid.

Syntax

-- Inline
CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    department_id  INT REFERENCES departments(department_id)
);

-- Table-level (more explicit, supports additional options)
CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    department_id  INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

Example

CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(100) NOT NULL
);

CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    first_name     VARCHAR(50) NOT NULL,
    department_id  INT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

-- First, insert departments
INSERT INTO departments VALUES (1, 'Engineering');
INSERT INTO departments VALUES (2, 'Marketing');

-- Works: department 1 exists
INSERT INTO employees VALUES (101, 'Alice', 1);

-- Fails: department 99 does not exist
INSERT INTO employees VALUES (102, 'Bob', 99);
ERROR: insert or update on table "employees" violates foreign key constraint
DETAIL: Key (department_id)=(99) is not present in table "departments".

Referential Actions: ON DELETE and ON UPDATE

When a referenced row in the parent table is deleted or updated, what should happen to the child rows? Foreign keys let you define this behavior explicitly.

CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    first_name     VARCHAR(50) NOT NULL,
    department_id  INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON DELETE SET NULL
        ON UPDATE CASCADE
);

Here is what each option does:

Action ON DELETE behavior ON UPDATE behavior
RESTRICT (default) Block the delete if child rows exist Block the update if child rows exist
NO ACTION Same as RESTRICT (checked at end of statement) Same as RESTRICT
CASCADE Delete all child rows automatically Update the foreign key in all child rows
SET NULL Set the foreign key to NULL in child rows Set the foreign key to NULL in child rows
SET DEFAULT Set the foreign key to its DEFAULT value Set the foreign key to its DEFAULT value

Practical examples:

-- CASCADE: deleting a department deletes all its employees
CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    department_id  INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON DELETE CASCADE
);

DELETE FROM departments WHERE department_id = 1;
-- All employees in department 1 are automatically deleted
-- SET NULL: deleting a department orphans employees but keeps them
CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    department_id  INT,      -- must be nullable for SET NULL to work
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON DELETE SET NULL
);

DELETE FROM departments WHERE department_id = 1;
-- Employees formerly in department 1 now have department_id = NULL
-- RESTRICT: cannot delete a department that has employees
CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    department_id  INT NOT NULL,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON DELETE RESTRICT
);

DELETE FROM departments WHERE department_id = 1;
-- ERROR: cannot delete because employees reference this department

Composite Foreign Keys

When the parent table has a composite primary key, the foreign key must reference all columns:

CREATE TABLE semesters (
    year       INT,
    term       VARCHAR(10),
    start_date DATE,
    PRIMARY KEY (year, term)
);

CREATE TABLE classes (
    class_id   INT PRIMARY KEY,
    year       INT NOT NULL,
    term       VARCHAR(10) NOT NULL,
    course_name VARCHAR(100),
    FOREIGN KEY (year, term) REFERENCES semesters(year, term)
);

Self-Referencing Foreign Keys

A table can reference itself. This is common for hierarchical data like organizational charts or category trees:

CREATE TABLE employees (
    employee_id   INT PRIMARY KEY,
    first_name    VARCHAR(50) NOT NULL,
    manager_id    INT,
    FOREIGN KEY (manager_id) REFERENCES employees(employee_id)
);

-- The CEO has no manager
INSERT INTO employees VALUES (1, 'Sarah', NULL);

-- Sarah manages Alice
INSERT INTO employees VALUES (2, 'Alice', 1);

-- Alice manages Bob
INSERT INTO employees VALUES (3, 'Bob', 2);

-- Fails: employee 999 does not exist
INSERT INTO employees VALUES (4, 'Carol', 999);
ERROR: insert or update on table "employees" violates foreign key constraint
DETAIL: Key (manager_id)=(999) is not present in table "employees".

CHECK

The CHECK constraint validates that column values meet a specific condition. Any insert or update that violates the condition is rejected.

Syntax

-- Inline (single column)
CREATE TABLE products (
    product_id   INT PRIMARY KEY,
    price        DECIMAL(10,2) CHECK (price >= 0),
    quantity     INT CHECK (quantity >= 0)
);

-- Table-level (can reference multiple columns)
CREATE TABLE projects (
    project_id   INT PRIMARY KEY,
    start_date   DATE NOT NULL,
    end_date     DATE,
    CHECK (end_date IS NULL OR end_date >= start_date)
);

Example: Single-Column Checks

CREATE TABLE products (
    product_id    INT PRIMARY KEY,
    product_name  VARCHAR(100) NOT NULL,
    price         DECIMAL(10,2) NOT NULL CHECK (price >= 0),
    discount_pct  DECIMAL(5,2) CHECK (discount_pct BETWEEN 0 AND 100),
    rating        DECIMAL(2,1) CHECK (rating >= 0 AND rating <= 5.0),
    status        VARCHAR(20) CHECK (status IN ('active', 'discontinued', 'draft'))
);

-- Works: all values pass validation
INSERT INTO products VALUES (1, 'Wireless Mouse', 25.99, 10.00, 4.5, 'active');

-- Fails: negative price
INSERT INTO products VALUES (2, 'USB Cable', -5.00, 0, 3.0, 'active');
ERROR: new row for relation "products" violates check constraint "products_price_check"
DETAIL: Failing row contains (2, USB Cable, -5.00, 0, 3.0, active).
-- Fails: invalid status
INSERT INTO products VALUES (3, 'Notebook', 4.50, 0, 4.0, 'banana');
ERROR: new row for relation "products" violates check constraint "products_status_check"
-- Fails: discount over 100%
INSERT INTO products VALUES (4, 'Keyboard', 89.99, 150.00, 4.2, 'active');
ERROR: new row for relation "products" violates check constraint "products_discount_pct_check"

Example: Multi-Column Checks

Table-level CHECK constraints can reference multiple columns, enabling cross-column validation:

CREATE TABLE events (
    event_id     INT PRIMARY KEY,
    event_name   VARCHAR(100) NOT NULL,
    start_date   DATE NOT NULL,
    end_date     DATE NOT NULL,
    min_capacity INT NOT NULL DEFAULT 1,
    max_capacity INT NOT NULL,
    CHECK (end_date >= start_date),
    CHECK (max_capacity >= min_capacity),
    CHECK (min_capacity >= 1)
);

-- Works: end_date after start_date, capacity valid
INSERT INTO events VALUES (1, 'SQL Workshop', '2024-07-01', '2024-07-03', 5, 30);

-- Fails: end_date before start_date
INSERT INTO events VALUES (2, 'Bad Event', '2024-07-10', '2024-07-05', 5, 30);
ERROR: new row for relation "events" violates check constraint "events_check"
-- Fails: max_capacity less than min_capacity
INSERT INTO events VALUES (3, 'Tiny Event', '2024-08-01', '2024-08-02', 20, 10);
ERROR: new row for relation "events" violates check constraint "events_check1"

CHECK on Updates Too

Like all constraints, CHECK is enforced on both inserts and updates:

-- Fails: cannot set price to negative via UPDATE either
UPDATE products SET price = -10.00 WHERE product_id = 1;
ERROR: new row for relation "products" violates check constraint "products_price_check"

DEFAULT

The DEFAULT constraint provides an automatic value for a column when an insert statement does not specify one. It does not prevent explicit values; it simply fills in the gap when no value is given.

Syntax

CREATE TABLE orders (
    order_id      INT PRIMARY KEY,
    status        VARCHAR(20) DEFAULT 'pending',
    priority      INT DEFAULT 0,
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    is_urgent     BOOLEAN DEFAULT FALSE
);

Example

CREATE TABLE tasks (
    task_id       INT PRIMARY KEY,
    title         VARCHAR(100) NOT NULL,
    status        VARCHAR(20) NOT NULL DEFAULT 'todo',
    priority      INT NOT NULL DEFAULT 0,
    created_at    TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    assigned_to   VARCHAR(50) DEFAULT NULL
);

-- Insert with only required values
INSERT INTO tasks (task_id, title) VALUES (1, 'Write documentation');

To verify:

SELECT * FROM tasks WHERE task_id = 1;
task_id title status priority created_at assigned_to
1 Write documentation todo 0 2024-06-15 10:30:00 NULL

All columns with defaults received their default values automatically.

Types of Default Values

CREATE TABLE examples (
    -- Static literal values
    status        VARCHAR(20) DEFAULT 'active',
    quantity      INT DEFAULT 0,
    is_public     BOOLEAN DEFAULT TRUE,
    tax_rate      DECIMAL(4,2) DEFAULT 0.00,

    -- Built-in functions
    created_at    TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    order_date    DATE DEFAULT CURRENT_DATE,

    -- NULL (explicit, same as no default for nullable columns)
    notes         TEXT DEFAULT NULL
);

DEFAULT vs Explicit NULL

An important distinction: DEFAULT fills in when a column is omitted from the insert. It does not prevent an explicit NULL:

-- Omitting the column: DEFAULT kicks in
INSERT INTO tasks (task_id, title) VALUES (2, 'Review code');
-- status = 'todo' (default applied)

-- Explicitly passing NULL: DEFAULT does NOT apply
INSERT INTO tasks (task_id, title, status) VALUES (3, 'Fix bug', NULL);
-- status = NULL (if the column allows NULL)
-- ERROR if status is NOT NULL

This is why DEFAULT and NOT NULL are frequently combined. NOT NULL blocks explicit NULLs. DEFAULT fills in omitted values. Together, they guarantee the column always has a meaningful value.

-- Belt and suspenders: always has a value
status VARCHAR(20) NOT NULL DEFAULT 'pending'

Naming Conventions for Constraints

When you create constraints without naming them, the database generates automatic names like products_pkey, employees_department_id_fkey, or products_price_check. These auto-generated names work fine for simple schemas, but in larger projects, explicit constraint names make error messages clearer, migrations easier to write, and schema management far more predictable.

Naming Patterns

A widely adopted convention is:

{table}_{column(s)}_{constraint_type}
Constraint Type Abbreviation Example Name
Primary Key pk employees_pk
Foreign Key fk employees_department_id_fk
Unique uq users_email_uq
Check ck products_price_ck
Not Null (usually unnamed) Typically not named explicitly
Default (usually unnamed) Typically not named explicitly

Applying Named Constraints

Use the CONSTRAINT keyword followed by the name:

CREATE TABLE employees (
    employee_id    INT,
    first_name     VARCHAR(50) NOT NULL,
    last_name      VARCHAR(50) NOT NULL,
    email          VARCHAR(100) NOT NULL,
    department_id  INT NOT NULL,
    salary         DECIMAL(10,2) NOT NULL,
    hire_date      DATE NOT NULL DEFAULT CURRENT_DATE,

    CONSTRAINT employees_pk
        PRIMARY KEY (employee_id),

    CONSTRAINT employees_email_uq
        UNIQUE (email),

    CONSTRAINT employees_department_id_fk
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE RESTRICT,

    CONSTRAINT employees_salary_ck
        CHECK (salary > 0)
);

Why Named Constraints Are Better

1. Clearer error messages.

Unnamed constraint error:

ERROR: new row violates check constraint "employees_check1"

Named constraint error:

ERROR: new row violates check constraint "employees_salary_ck"

The named version immediately tells you which business rule was violated.

2. Easier to modify or drop.

-- With a named constraint, you can reference it directly
ALTER TABLE employees DROP CONSTRAINT employees_salary_ck;

ALTER TABLE employees ADD CONSTRAINT employees_salary_ck
    CHECK (salary >= 0);

Without a name, you first have to look up the auto-generated name, which varies by database and can change during migrations.

3. Migration scripts are more readable.

-- Clear intent: we know exactly what we're dropping
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fk;

-- Versus: cryptic auto-generated name
ALTER TABLE orders DROP CONSTRAINT orders_customer_id_fkey1;

Consistency Across a Schema

Here is a complete schema example with consistent naming:

CREATE TABLE departments (
    department_id   INT,
    department_name VARCHAR(100) NOT NULL,
    budget          DECIMAL(12,2) NOT NULL,

    CONSTRAINT departments_pk
        PRIMARY KEY (department_id),

    CONSTRAINT departments_name_uq
        UNIQUE (department_name),

    CONSTRAINT departments_budget_ck
        CHECK (budget >= 0)
);

CREATE TABLE employees (
    employee_id    INT,
    first_name     VARCHAR(50) NOT NULL,
    last_name      VARCHAR(50) NOT NULL,
    email          VARCHAR(100) NOT NULL,
    department_id  INT NOT NULL,
    salary         DECIMAL(10,2) NOT NULL,
    hire_date      DATE NOT NULL DEFAULT CURRENT_DATE,

    CONSTRAINT employees_pk
        PRIMARY KEY (employee_id),

    CONSTRAINT employees_email_uq
        UNIQUE (email),

    CONSTRAINT employees_department_id_fk
        FOREIGN KEY (department_id)
        REFERENCES departments(department_id)
        ON DELETE RESTRICT
        ON UPDATE CASCADE,

    CONSTRAINT employees_salary_ck
        CHECK (salary > 0)
);

CREATE TABLE projects (
    project_id    INT,
    project_name  VARCHAR(100) NOT NULL,
    start_date    DATE NOT NULL,
    end_date      DATE,
    status        VARCHAR(20) NOT NULL DEFAULT 'planning',

    CONSTRAINT projects_pk
        PRIMARY KEY (project_id),

    CONSTRAINT projects_name_uq
        UNIQUE (project_name),

    CONSTRAINT projects_dates_ck
        CHECK (end_date IS NULL OR end_date >= start_date),

    CONSTRAINT projects_status_ck
        CHECK (status IN ('planning', 'active', 'completed', 'cancelled'))
);

CREATE TABLE project_assignments (
    employee_id    INT,
    project_id     INT,
    role           VARCHAR(50) NOT NULL,
    assigned_date  DATE NOT NULL DEFAULT CURRENT_DATE,

    CONSTRAINT project_assignments_pk
        PRIMARY KEY (employee_id, project_id),

    CONSTRAINT project_assignments_employee_id_fk
        FOREIGN KEY (employee_id)
        REFERENCES employees(employee_id)
        ON DELETE CASCADE,

    CONSTRAINT project_assignments_project_id_fk
        FOREIGN KEY (project_id)
        REFERENCES projects(project_id)
        ON DELETE CASCADE
);

Every constraint is named, the pattern is consistent, and any error message will immediately point to the specific rule that was violated.

Adding and Removing Constraints on Existing Tables

You do not have to define all constraints at table creation time. The ALTER TABLE statement lets you add, modify, or remove constraints on existing tables.

Adding Constraints

-- Add a primary key
ALTER TABLE departments
ADD CONSTRAINT departments_pk PRIMARY KEY (department_id);

-- Add a foreign key
ALTER TABLE employees
ADD CONSTRAINT employees_department_id_fk
    FOREIGN KEY (department_id) REFERENCES departments(department_id);

-- Add a unique constraint
ALTER TABLE employees
ADD CONSTRAINT employees_email_uq UNIQUE (email);

-- Add a check constraint
ALTER TABLE products
ADD CONSTRAINT products_price_ck CHECK (price >= 0);

-- Add NOT NULL (syntax varies by database)
-- PostgreSQL:
ALTER TABLE employees ALTER COLUMN first_name SET NOT NULL;

-- MySQL:
ALTER TABLE employees MODIFY first_name VARCHAR(50) NOT NULL;

-- SQL Server:
ALTER TABLE employees ALTER COLUMN first_name VARCHAR(50) NOT NULL;

Removing Constraints

-- Drop by name
ALTER TABLE employees DROP CONSTRAINT employees_email_uq;
ALTER TABLE employees DROP CONSTRAINT employees_department_id_fk;
ALTER TABLE products  DROP CONSTRAINT products_price_ck;

-- Drop primary key (syntax varies)
-- PostgreSQL / SQL Server:
ALTER TABLE departments DROP CONSTRAINT departments_pk;

-- MySQL:
ALTER TABLE departments DROP PRIMARY KEY;

-- Remove NOT NULL
-- PostgreSQL:
ALTER TABLE employees ALTER COLUMN email DROP NOT NULL;

-- MySQL:
ALTER TABLE employees MODIFY email VARCHAR(100) NULL;

Temporarily Disabling Constraints

Some databases allow you to disable constraints without dropping them, which is useful during bulk data loads:

-- PostgreSQL: disable all triggers (which enforce FK constraints)
ALTER TABLE employees DISABLE TRIGGER ALL;
-- ... bulk load ...
ALTER TABLE employees ENABLE TRIGGER ALL;

-- MySQL: disable foreign key checks globally for the session
SET FOREIGN_KEY_CHECKS = 0;
-- ... bulk load ...
SET FOREIGN_KEY_CHECKS = 1;

-- SQL Server: disable specific constraint
ALTER TABLE employees NOCHECK CONSTRAINT employees_department_id_fk;
-- ... bulk load ...
ALTER TABLE employees CHECK CONSTRAINT employees_department_id_fk;

Viewing Existing Constraints

To inspect constraints on a table:

-- PostgreSQL
SELECT conname, contype, pg_get_constraintdef(oid)
FROM pg_constraint
WHERE conrelid = 'employees'::regclass;

-- MySQL
SELECT constraint_name, constraint_type
FROM information_schema.table_constraints
WHERE table_name = 'employees';

-- SQL Server
SELECT name, type_desc
FROM sys.objects
WHERE parent_object_id = OBJECT_ID('employees')
  AND type IN ('PK', 'UQ', 'F', 'C');

-- SQLite
SELECT sql FROM sqlite_master WHERE name = 'employees';

Common Mistakes

Mistake 1: Forgetting to Create the Referenced Table First

Foreign keys require the referenced table to exist:

Wrong order:

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)   -- customers doesn't exist yet!
);

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(100)
);
ERROR: relation "customers" does not exist

Correct order:

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    name        VARCHAR(100)
);

CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    customer_id INT REFERENCES customers(customer_id)
);

Mistake 2: SET NULL on a NOT NULL Foreign Key Column

If the foreign key column is NOT NULL, ON DELETE SET NULL will fail when triggered:

CREATE TABLE employees (
    employee_id    INT PRIMARY KEY,
    department_id  INT NOT NULL,                          -- NOT NULL!
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
        ON DELETE SET NULL                                -- Contradiction!
);

-- Inserting works fine
INSERT INTO employees VALUES (1, 1);

-- But deleting the department fails
DELETE FROM departments WHERE department_id = 1;
ERROR: null value in column "department_id" violates not-null constraint

Fix: Either make the column nullable or use a different referential action:

-- Option A: allow NULL
department_id  INT,     -- nullable
FOREIGN KEY (department_id) REFERENCES departments(department_id)
    ON DELETE SET NULL

-- Option B: use CASCADE or RESTRICT instead
department_id  INT NOT NULL,
FOREIGN KEY (department_id) REFERENCES departments(department_id)
    ON DELETE RESTRICT

Mistake 3: Over-Constraining with CHECK

Overly strict CHECK constraints can make legitimate data entry impossible:

Too strict:

CREATE TABLE contacts (
    phone VARCHAR(15) CHECK (LENGTH(phone) = 10)
    -- Rejects international numbers, numbers with extensions, etc.
);

Better:

CREATE TABLE contacts (
    phone VARCHAR(20) CHECK (LENGTH(phone) >= 7 AND LENGTH(phone) <= 20)
    -- Flexible enough for various formats
);

Mistake 4: Not Naming Constraints

Unnamed (auto-generated names are cryptic):

CREATE TABLE products (
    product_id INT PRIMARY KEY,
    price      DECIMAL(10,2) CHECK (price >= 0),
    UNIQUE (product_id)
);
-- Constraint names: products_pkey, products_price_check, products_product_id_key
-- These vary by database and are hard to reference in migrations

Named (clear and predictable):

CREATE TABLE products (
    product_id INT,
    price      DECIMAL(10,2),

    CONSTRAINT products_pk PRIMARY KEY (product_id),
    CONSTRAINT products_price_ck CHECK (price >= 0)
);

Mistake 5: Circular Foreign Keys

Two tables that reference each other create a chicken-and-egg problem:

CREATE TABLE husbands (
    id       INT PRIMARY KEY,
    wife_id  INT REFERENCES wives(id)    -- wives doesn't exist yet!
);

CREATE TABLE wives (
    id          INT PRIMARY KEY,
    husband_id  INT REFERENCES husbands(id)
);

Solution: Create the tables without the foreign keys first, then add them:

CREATE TABLE husbands (
    id       INT PRIMARY KEY,
    wife_id  INT
);

CREATE TABLE wives (
    id          INT PRIMARY KEY,
    husband_id  INT
);

-- Add foreign keys after both tables exist
ALTER TABLE husbands ADD CONSTRAINT husbands_wife_id_fk
    FOREIGN KEY (wife_id) REFERENCES wives(id);

ALTER TABLE wives ADD CONSTRAINT wives_husband_id_fk
    FOREIGN KEY (husband_id) REFERENCES husbands(id);

Quick Reference

Constraint Purpose Allows NULL Multiple Per Table Applied To
PRIMARY KEY Unique row identifier No One only Column(s)
NOT NULL Prevents NULL values No (by definition) Unlimited Column
UNIQUE Prevents duplicate values Yes (usually) Unlimited Column(s)
FOREIGN KEY Enforces referential integrity Yes (unless NOT NULL) Unlimited Column(s)
CHECK Validates value conditions N/A Unlimited Column(s)
DEFAULT Auto-fills omitted values N/A One per column Column

Summary

SQL constraints are the database-level rules that ensure your data stays clean, consistent, and reliable. They catch invalid data automatically, regardless of how or where the data originates.

Key takeaways:

  • PRIMARY KEY uniquely identifies every row. It enforces both uniqueness and non-nullability. Every table should have one.
  • NOT NULL prevents a column from storing NULL values. Use it on every column that must always have a value.
  • UNIQUE ensures no two rows share the same value in a column or combination of columns. A table can have multiple unique constraints.
  • FOREIGN KEY links tables together and enforces referential integrity. Configure ON DELETE and ON UPDATE actions (CASCADE, SET NULL, RESTRICT) to define how changes to parent rows affect child rows.
  • CHECK validates that values meet specific conditions (non-negative prices, valid status values, date ranges). Supported in all modern databases (MySQL 8.0.16+).
  • DEFAULT provides automatic values for omitted columns. Combine with NOT NULL to guarantee columns always have meaningful values.
  • Name your constraints using a consistent pattern like {table}_{column}_{type}. Named constraints produce clearer error messages, make migrations easier, and keep your schema professional.
  • Constraints can be added and removed on existing tables using ALTER TABLE. Always verify that existing data complies before adding a new constraint.
  • Define constraints at the database level, not just in application code. Application-level validation can be bypassed by direct SQL queries, data migrations, and other data entry points. Database constraints are always enforced.

Well-defined constraints are the difference between a database that protects your data and one that silently accumulates garbage. Invest the time to set them up properly, and they will save you from countless data integrity bugs throughout the life of your application.

Navigation