SQL Constraints for Ensuring Data Integrity

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.

tip

A table can have only one primary key, but that primary key can consist of multiple columns. If you need additional uniqueness guarantees on other columns, use the UNIQUE constraint.

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

info

NOT NULL and DEFAULT work well together. If a column should always have a value but the application might not always provide one, combine them:

status     VARCHAR(20) NOT NULL DEFAULT 'active',
created_at TIMESTAMP   NOT NULL DEFAULT CURRENT_TIMESTAMP

The column is never NULL: if the insert omits the value, the default fills in. If the insert explicitly passes NULL, the NOT NULL constraint rejects it.

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)

warning

In SQL Server, a UNIQUE constraint treats NULL as a value, so only one NULL is allowed by default. To allow multiple NULLs, create a filtered unique index instead:

-- SQL Server: allow multiple NULLs with a filtered index
CREATE UNIQUE INDEX idx_contacts_phone
ON contacts (phone)
WHERE phone IS NOT NULL;

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

danger

ON DELETE CASCADE is powerful but dangerous. Deleting a single parent row can silently wipe out thousands of child rows across multiple tables if cascades chain together. Always use it intentionally and document the behavior. For critical data, RESTRICT (the default) is usually safer because it forces you to handle dependencies explicitly.

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"

info

MySQL note: CHECK constraints are parsed but ignored in MySQL versions before 8.0.16. If you are using MySQL 5.7 or earlier, CHECK constraints are accepted syntactically but never enforced. This means invalid data will be silently stored. Upgrade to MySQL 8.0.16+ for proper CHECK support, or use triggers as a workaround on older versions.

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
);

tip

Some databases support more complex default expressions:

-- PostgreSQL: function call as default
CREATE TABLE tokens (
    token_id    INT PRIMARY KEY,
    token       UUID DEFAULT gen_random_uuid(),
    expires_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP + INTERVAL '24 hours'
);

-- MySQL 8.0+: expression defaults
CREATE TABLE orders (
    order_id    INT PRIMARY KEY,
    order_date  DATE DEFAULT (CURRENT_DATE)
);

Check your database's documentation for supported default expressions.

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;

tip

Always name your constraints for primary keys, foreign keys, unique constraints, and check constraints. The few extra characters in your CREATE TABLE statement save significant time when debugging errors, writing migrations, and maintaining schemas.

For NOT NULL and DEFAULT, explicit naming is not necessary in most databases because they are modified using ALTER COLUMN syntax rather than DROP CONSTRAINT.

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;

warning

Adding a constraint to an existing table with data will fail if any existing rows violate the new constraint. You must fix the violating data first.

-- Check for violations before adding the constraint
SELECT * FROM products WHERE price < 0;

-- Fix them
UPDATE products SET price = 0 WHERE price < 0;

-- Now safely add the constraint
ALTER TABLE products ADD CONSTRAINT products_price_ck CHECK (price >= 0);

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;

danger

After re-enabling constraints, the database does not automatically validate existing data against the re-enabled constraint (in most databases). Data loaded while the constraint was disabled may violate it. Validate manually:

-- SQL Server: check for violations
ALTER TABLE employees WITH CHECK CHECK CONSTRAINT employees_department_id_fk;

-- PostgreSQL: validate manually
SELECT e.employee_id, e.department_id
FROM employees e
LEFT JOIN departments d ON e.department_id = d.department_id
WHERE d.department_id IS NULL;

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.

Why Constraints Matter​

The Sample Schema​

PRIMARY KEY​

Syntax​

Example​

Composite Primary Keys​

Auto-Generated Primary Keys​

NOT NULL​

Syntax​

Example​

When to Use NOT NULL​

NOT NULL on UPDATE​

UNIQUE​

Syntax​

Example​

Multi-Column UNIQUE Constraints​

UNIQUE vs PRIMARY KEY​

NULLs in UNIQUE Columns​

FOREIGN KEY​

Syntax​

Example​

Referential Actions: ON DELETE and ON UPDATE​

Composite Foreign Keys​

Self-Referencing Foreign Keys​

CHECK​

Syntax​

Example: Single-Column Checks​

Example: Multi-Column Checks​

CHECK on Updates Too​

DEFAULT​

Syntax​

Example​

Types of Default Values​

DEFAULT vs Explicit NULL​

Naming Conventions for Constraints​

Naming Patterns​

Applying Named Constraints​

Why Named Constraints Are Better​

Consistency Across a Schema​

Adding and Removing Constraints on Existing Tables​

Adding Constraints​

Removing Constraints​

Temporarily Disabling Constraints​

Viewing Existing Constraints​

Common Mistakes​

Mistake 1: Forgetting to Create the Referenced Table First​

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

Mistake 3: Over-Constraining with CHECK​

Mistake 4: Not Naming Constraints​

Mistake 5: Circular Foreign Keys​

Quick Reference​

Summary​

Table of Contents

Why Constraints Matter

The Sample Schema

PRIMARY KEY

Syntax

Example

Composite Primary Keys

Auto-Generated Primary Keys

NOT NULL

Syntax

Example

When to Use NOT NULL

NOT NULL on UPDATE

UNIQUE

Syntax

Example

Multi-Column UNIQUE Constraints

UNIQUE vs PRIMARY KEY

NULLs in UNIQUE Columns

FOREIGN KEY

Syntax

Example

Referential Actions: ON DELETE and ON UPDATE

Composite Foreign Keys

Self-Referencing Foreign Keys

CHECK

Syntax

Example: Single-Column Checks

Example: Multi-Column Checks

CHECK on Updates Too

DEFAULT

Syntax

Example

Types of Default Values

DEFAULT vs Explicit NULL

Naming Conventions for Constraints

Naming Patterns

Applying Named Constraints

Why Named Constraints Are Better

Consistency Across a Schema

Adding and Removing Constraints on Existing Tables

Adding Constraints

Removing Constraints

Temporarily Disabling Constraints

Viewing Existing Constraints

Common Mistakes

Mistake 1: Forgetting to Create the Referenced Table First

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

Mistake 3: Over-Constraining with CHECK

Mistake 4: Not Naming Constraints

Mistake 5: Circular Foreign Keys

Quick Reference

Summary