SQL — Constraints

What are constraints?

Constraints are rules that ensure data stays valid. They prevent invalid data from entering your database:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    email VARCHAR(255) UNIQUE,
    age INT CHECK (age >= 0 AND age <= 150),
    grade VARCHAR(10) DEFAULT 'F'
);

PRIMARY KEY

Uniquely identifies each row. No duplicates, no NULLs:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(100) NOT NULL
);

A table can have only one primary key. It can span multiple columns:

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    PRIMARY KEY (student_id, course_id)
);

FOREIGN KEY

Links a column to a primary key in another table:

CREATE TABLE enrollments (
    id INT PRIMARY KEY AUTO_INCREMENT,
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id),
    FOREIGN KEY (course_id) REFERENCES courses(id)
);

Foreign keys enforce referential integrity — you can't enroll a student that doesn't exist.

ON DELETE and ON UPDATE

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    FOREIGN KEY (student_id) REFERENCES students(id)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id)
        ON DELETE SET NULL
);

Action	Behavior
CASCADE	Delete/update matching rows automatically
SET NULL	Set foreign key to NULL
SET DEFAULT	Set foreign key to default value
RESTRICT	Prevent deletion if references exist
NO ACTION	Same as RESTRICT (default)

NOT NULL

Prevents NULL values:

CREATE TABLE users (
    id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(255) NOT NULL
);

Every required field should be NOT NULL.

UNIQUE

Ensures no duplicate values:

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255) UNIQUE,
    username VARCHAR(50) UNIQUE
);

UNIQUE allows NULLs (multiple NULLs are allowed). PRIMARY KEY does not.

Named unique constraint

CREATE TABLE users (
    id INT PRIMARY KEY,
    email VARCHAR(255),
    CONSTRAINT unique_email UNIQUE (email)
);

Naming constraints makes error messages clearer and allows you to drop them later.

CHECK

Validates data against a condition:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2) CHECK (price > 0),
    stock INT CHECK (stock >= 0),
    category VARCHAR(50) CHECK (category IN ('Electronics', 'Clothing', 'Food'))
);

CHECK constraints are evaluated on INSERT and UPDATE. Invalid data is rejected.

DEFAULT

Provides a value when none is specified:

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(20) DEFAULT 'pending',
    quantity INT DEFAULT 1,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Adding constraints to existing tables

-- Add NOT NULL
ALTER TABLE students MODIFY COLUMN name VARCHAR(100) NOT NULL;

-- Add UNIQUE
ALTER TABLE students ADD CONSTRAINT unique_email UNIQUE (email);

-- Add CHECK
ALTER TABLE students ADD CONSTRAINT check_age CHECK (age >= 0);

-- Add DEFAULT
ALTER TABLE students ALTER COLUMN grade SET DEFAULT 'F';

-- Add foreign key
ALTER TABLE enrollments
ADD CONSTRAINT fk_student
FOREIGN KEY (student_id) REFERENCES students(id);

Dropping constraints

-- Drop primary key
ALTER TABLE students DROP PRIMARY KEY;

-- Drop named constraint
ALTER TABLE students DROP CONSTRAINT unique_email;

-- Drop foreign key
ALTER TABLE enrollments DROP FOREIGN KEY fk_student;

-- Drop CHECK constraint
ALTER TABLE students DROP CONSTRAINT check_age;

Composite unique constraint

CREATE TABLE enrollments (
    student_id INT,
    course_id INT,
    semester VARCHAR(20),
    UNIQUE (student_id, course_id, semester)
);

Each combination must be unique — a student can't enroll in the same course twice in one semester.

Deferrable constraints

CREATE TABLE accounts (
    id INT PRIMARY KEY,
    balance DECIMAL(10, 2) CHECK (balance >= 0) DEFERRABLE
);

Deferrable constraints can be checked at commit time instead of immediately. Useful for complex transactions.

Constraint naming

Always name your constraints:

CREATE TABLE products (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    price DECIMAL(10, 2),
    CONSTRAINT chk_price CHECK (price > 0),
    CONSTRAINT uq_name UNIQUE (name)
);

Unnamed constraints get auto-generated names like products_chk_12345. Named constraints are easier to identify in error messages and manage later.

Common patterns

Soft deletes with constraints

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL,
    deleted_at TIMESTAMP NULL,
    CONSTRAINT uq_active_email UNIQUE (email, deleted_at)
);

Audit columns

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_INCREMENT,
    customer_id INT NOT NULL,
    total DECIMAL(10, 2) NOT NULL CHECK (total >= 0),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (customer_id) REFERENCES customers(id)
);

Mini Practice

1. Create a products table with NOT NULL, UNIQUE, CHECK, and DEFAULT constraints
2. Add a foreign key constraint linking orders to customers
3. Create a composite unique constraint on two columns
4. Add a CHECK constraint that validates an email contains '@'
5. Drop a constraint from an existing table and verify it's gone

Next: indexes — speeding up queries →