Tutorials SQL SQL Constraints
Ch 9 / 69
Advertisement
📢Ad space · 728 × 90 · Replace slot ID above
Chapter 9 Beginner 9 min read DDL

SQL Constraints

Bad data is worse than no data — it corrupts reports, breaks applications, and is nearly impossible to fix after the fact. SQL constraints are the database's built-in enforcement system: rules attached to columns and tables that the engine validates automatically on every INSERT, UPDATE, and DELETE. In this chapter you'll master all 6 constraint types, learn how to name them, add them to existing tables, and understand the power of referential integrity through foreign keys.

What you'll learn
What SQL constraints are and why they matter
PRIMARY KEY — unique, non-null row identifier
NOT NULL — preventing empty values
UNIQUE — no duplicate values
DEFAULT — automatic fallback values
CHECK — custom value validation
FOREIGN KEY — referential integrity between tables
ON DELETE / ON UPDATE referential actions
Named constraints and ALTER TABLE management

What Are SQL Constraints?

A constraint is a rule that the database enforces on the data stored in a table. When an INSERT, UPDATE, or DELETE statement would violate a constraint, the entire operation is rejected and an error is returned — the row is never written.

Constraints are the database's immune system: they prevent bad data from ever entering the system, removing the burden from application code. They are defined when a table is created (or added later with ALTER TABLE).

PRIMARY KEY PK
Uniquely identifies every row. Implicitly NOT NULL + UNIQUE. One per table.
NOT NULL NN
Column must always have a value. NULL is rejected.
UNIQUE UQ
No two rows may share the same value. NULL is allowed.
DEFAULT DF
Fallback value used automatically when nothing is provided.
CHECK CK
Validates a condition before storing. Rejects values that fail.
FOREIGN KEY FK
Links a column to another table's PK. Enforces referential integrity.
Key Principle
Constraints operate at the database engine level — they are enforced regardless of which application, user, or API inserts data. This makes them far more reliable than validation code written only in the application layer.

PRIMARY KEY Constraint

PRIMARY KEY

The PRIMARY KEY is the most important constraint. It uniquely identifies every row in a table — no two rows can share the same primary key value, and the key column(s) can never be NULL. A table should have exactly one primary key.

✓ Unique value per row ✓ Cannot be NULL ✓ Only 1 per table ✓ Auto-creates an index ✓ Can span multiple columns (composite)
SQL — PRIMARY KEY variations
-- ① Column-level (single-column PK)
CREATE TABLE students (
    student_id  INT  NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100) NOT NULL
);

-- ② Table-level (preferred — allows naming)
CREATE TABLE students (
    student_id  INT  NOT NULL AUTO_INCREMENT,
    name        VARCHAR(100) NOT NULL,
    CONSTRAINT pk_students PRIMARY KEY (student_id)
);

-- ③ Composite PRIMARY KEY (two-column PK)
CREATE TABLE order_items (
    order_id    INT NOT NULL,
    product_id  INT NOT NULL,
    quantity    INT NOT NULL DEFAULT 1,
    CONSTRAINT pk_order_items PRIMARY KEY (order_id, product_id)
);
AUTO_INCREMENT
MySQL automatically assigns the next integer (1, 2, 3…) when you don't provide a value. PostgreSQL uses SERIAL or GENERATED ALWAYS AS IDENTITY. Ensures every row gets a unique ID without manual management.
Composite PK
A primary key spanning two or more columns — the combination must be unique. Required for junction tables (many-to-many). Must be declared at table level, not inline on the column.
CONSTRAINT pk_name
Giving the constraint an explicit name makes error messages readable and allows you to drop/modify it later: ALTER TABLE students DROP PRIMARY KEY;

NOT NULL Constraint

NOT NULL

The NOT NULL constraint prevents a column from accepting NULL values. Any INSERT or UPDATE that omits this column (or explicitly passes NULL) is rejected. Without NOT NULL, columns accept NULL by default.

✓ Column must always have a value ✓ NULL is rejected on INSERT and UPDATE ✓ Can be combined with DEFAULT ✓ Column-level only — not table-level
SQL — NOT NULL in action
CREATE TABLE employees (
    emp_id    INT           AUTO_INCREMENT PRIMARY KEY,
    name      VARCHAR(100)  NOT NULL,      -- required
    email     VARCHAR(150)  NOT NULL,      -- required
    phone     VARCHAR(20),               -- optional (NULL allowed)
    dept      VARCHAR(80)   NOT NULL
                               DEFAULT 'General' -- required, but has default
);

-- ✅ This works — dept defaults to 'General'
INSERT INTO employees (name, email) VALUES ('Priya', 'priya@co.in');

-- ❌ This fails — name is NOT NULL
INSERT INTO employees (email) VALUES ('test@co.in');
Best Practice
Mark every column NOT NULL by default — only allow NULL when you genuinely have optional data (e.g. a phone number that may not exist). NULL in columns you expect to always have values causes silent bugs in WHERE filters and aggregations.
Advertisement
📢Ad space · 728 × 90 · Replace slot ID above

UNIQUE Constraint

UNIQUE

The UNIQUE constraint ensures no two rows can have the same value in a column (or combination of columns). Unlike PRIMARY KEY, a UNIQUE column can contain NULL — and most databases allow multiple NULL values (since NULL is considered "unknown", not equal to anything). A table can have multiple UNIQUE constraints.

✓ No duplicate non-NULL values ✓ NULL allowed (multiple NULLs OK in MySQL) ✓ Multiple per table ✓ Auto-creates an index ✓ Can span multiple columns (composite unique)
SQL — UNIQUE constraint examples
CREATE TABLE users (
    user_id   INT           AUTO_INCREMENT PRIMARY KEY,
    -- Single-column UNIQUE (inline)
    email     VARCHAR(150)  NOT NULL UNIQUE,
    username  VARCHAR(50)   NOT NULL,
    phone     VARCHAR(20)   UNIQUE,   -- optional but must be unique if provided
    -- Named composite UNIQUE (table-level) — pair must be unique
    CONSTRAINT uq_username UNIQUE (username),
    CONSTRAINT uq_user_phone_region UNIQUE (phone, country_code)
);
Feature PRIMARY KEY UNIQUE
Unique values ✅ Yes (enforced) ✅ Yes (enforced)
NULL allowed ❌ Never ✅ Yes (one or more)
Count per table Exactly 1 Unlimited
Creates index ✅ Clustered index ✅ Non-clustered index
Composite keys ✅ Yes ✅ Yes
Typical use Row identity (ID) Business keys (email, username)

DEFAULT Constraint

DEFAULT

The DEFAULT constraint specifies a value to use automatically when an INSERT statement does not supply one for that column. It accepts literal values, SQL functions (CURRENT_TIMESTAMP, CURRENT_DATE), or expressions.

✓ Literal values: 0, 'active', TRUE ✓ SQL functions: CURRENT_TIMESTAMP ✓ Can be combined with NOT NULL ✓ Overridden if a value is explicitly supplied
SQL — DEFAULT constraint in practice
CREATE TABLE orders (
    order_id    INT      AUTO_INCREMENT PRIMARY KEY,
    customer_id INT      NOT NULL,
    -- Default literal values
    status      ENUM('pending','paid','shipped','cancelled')
                             NOT NULL DEFAULT 'pending',
    discount    DECIMAL(5,2) NOT NULL DEFAULT 0.00,
    is_gift     BOOLEAN      NOT NULL DEFAULT FALSE,
    -- Default SQL function values
    order_date  DATE         NOT NULL DEFAULT (CURRENT_DATE),
    created_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP,
    updated_at  TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
                             ON UPDATE CURRENT_TIMESTAMP
);

-- Only customer_id is required — everything else is defaulted
INSERT INTO orders (customer_id) VALUES (42);
-- Result: status='pending', discount=0.00, is_gift=FALSE, order_date=today

CHECK Constraint

CHECK

The CHECK constraint allows you to define a custom boolean condition that every value must satisfy. If a row is inserted or updated with a value that makes the condition evaluate to FALSE, the operation is rejected. CHECK conditions can reference any column in the same table.

✓ Any boolean expression ✓ Can reference multiple columns ✓ Supports BETWEEN, IN, AND, OR ⚠️ MySQL < 8.0.16 parsed but ignored CHECK
SQL — CHECK constraint examples
CREATE TABLE students (
    student_id  INT           AUTO_INCREMENT PRIMARY KEY,
    name        VARCHAR(100)  NOT NULL,
    -- Range check: age must be between 5 and 100
    age         TINYINT UNSIGNED CHECK (age BETWEEN 5 AND 100),
    -- IN list check: grade must be one of these values
    grade       CHAR(2)
                CHECK (grade IN ('A+','A','B+','B','C','D','F')),
    -- Named table-level CHECK referencing two columns
    start_date  DATE          NOT NULL,
    end_date    DATE,
    CONSTRAINT chk_date_order
        CHECK (end_date IS NULL OR end_date > start_date)
);
MySQL Version Note
MySQL silently ignored CHECK constraints until version 8.0.16 (released 2019). Always test on your target version. If you need CHECK-like behavior on older MySQL, use ENUM or TRIGGER instead.
Advertisement
📢Ad space · 728 × 90 · Replace slot ID above

FOREIGN KEY Constraint & Referential Integrity

FOREIGN KEY

A FOREIGN KEY links a column in one table (the child) to the PRIMARY KEY of another table (the parent). This creates a guaranteed relationship: you cannot insert a child row that references a non-existent parent, and you cannot delete a parent that still has children (unless you define a referential action).

✓ References parent table's PK or UNIQUE column ✓ Prevents orphaned child rows ✓ Must be declared at table level ✓ Always create parent table first ✓ Supports 4 referential actions
FOREIGN KEY — Parent ↔ Child Relationship
customers (parent table)
customer_id name email
101 Priya Sharma priya@mail.in
102 Rahul Patel rahul@mail.in
103 Anika Desai anika@mail.in
orders (child table)
order_id customer_id amount
1001 101 ₹3,499
1002 101 ₹1,299
1003 102 ₹5,799
1004 999 ← FK violation! ₹899
customers.customer_id → orders.customer_id

Row 1004 would be rejected — customer 999 does not exist in the customers table

SQL — FOREIGN KEY with named constraint
-- Parent table created first
CREATE TABLE customers (
    customer_id  INT           AUTO_INCREMENT PRIMARY KEY,
    name         VARCHAR(100)  NOT NULL,
    email        VARCHAR(150)  NOT NULL UNIQUE
);

-- Child table references parent's PK
CREATE TABLE orders (
    order_id    INT           AUTO_INCREMENT PRIMARY KEY,
    customer_id INT           NOT NULL,
    amount      DECIMAL(10,2) NOT NULL,
    status      ENUM('pending','paid','shipped') DEFAULT 'pending',
    created_at  TIMESTAMP     DEFAULT CURRENT_TIMESTAMP,
    CONSTRAINT fk_orders_customer
        FOREIGN KEY (customer_id)
        REFERENCES  customers(customer_id)
        ON DELETE   RESTRICT
        ON UPDATE   CASCADE
);

Referential Actions — ON DELETE & ON UPDATE

Referential actions control what happens to child rows when the referenced parent row is deleted or updated:

RESTRICT (default)
Prevents the parent row from being deleted/updated if any child rows reference it. The operation fails with an error. Safest option — use by default.
CASCADE
Automatically deletes/updates all child rows when the parent is deleted/updated. Perfect for dependent data (e.g. delete a user → delete their posts too).
SET NULL
Sets the foreign key column to NULL in all child rows when the parent is deleted/updated. The FK column must allow NULL.
SET DEFAULT
Sets the FK column to its DEFAULT value when the parent is deleted/updated. Rarely used in MySQL/PostgreSQL.
NO ACTION
Deferred version of RESTRICT (checked at end of transaction). Behaves identically to RESTRICT in MySQL (which doesn't support deferred constraints).
SQL — Referential action examples
-- ON DELETE CASCADE: deleting user deletes all their posts
CREATE TABLE posts (
    post_id  INT  AUTO_INCREMENT PRIMARY KEY,
    user_id  INT  NOT NULL,
    title    VARCHAR(200) NOT NULL,
    CONSTRAINT fk_post_user
        FOREIGN KEY (user_id)
        REFERENCES  users(user_id)
        ON DELETE CASCADE
        ON UPDATE CASCADE
);

-- ON DELETE SET NULL: department deleted → employee.dept_id becomes NULL
CREATE TABLE employees (
    emp_id   INT  AUTO_INCREMENT PRIMARY KEY,
    name     VARCHAR(100) NOT NULL,
    dept_id  INT,               -- NULL allowed so SET NULL works
    CONSTRAINT fk_emp_dept
        FOREIGN KEY (dept_id)
        REFERENCES  departments(dept_id)
        ON DELETE SET NULL
);

Named Constraints — Why & How

Every constraint can be given an explicit name using the CONSTRAINT constraint_name keyword before the constraint type. This makes error messages readable, allows you to drop/modify specific constraints later, and serves as self-documenting code.

Readable Errors
Without a name, MySQL generates a cryptic identifier like orders_ibfk_1. With a name like fk_orders_customer, the error message instantly tells you which constraint failed.
Easier to Alter
To drop a constraint you need its name. Named constraints are essential for ALTER TABLE … DROP CONSTRAINT name without guessing the auto-generated identifier.
Self-Documenting
Good naming conventions (pk_, fk_, uq_, chk_, df_) document the schema's intent and make it readable to any developer.
SQL — Naming conventions in a complete table
CREATE TABLE products (
    product_id   INT            NOT NULL AUTO_INCREMENT,
    sku          VARCHAR(40)    NOT NULL,
    name         VARCHAR(150)   NOT NULL,
    price        DECIMAL(10,2)  NOT NULL,
    stock        INT            NOT NULL DEFAULT 0,
    category_id  INT,

    -- pk_  prefix for primary keys
    CONSTRAINT pk_products           PRIMARY KEY (product_id),
    -- uq_  prefix for unique constraints
    CONSTRAINT uq_products_sku       UNIQUE (sku),
    -- chk_ prefix for check constraints
    CONSTRAINT chk_products_price     CHECK (price > 0),
    CONSTRAINT chk_products_stock     CHECK (stock >= 0),
    -- fk_  prefix for foreign keys
    CONSTRAINT fk_products_category
        FOREIGN KEY (category_id)
        REFERENCES  categories(category_id)
        ON DELETE SET NULL
);

Adding & Removing Constraints with ALTER TABLE

You don't have to define all constraints at table creation time. You can add constraints to existing tables using ALTER TABLE. However, existing data must already satisfy the new constraint — otherwise the ALTER will fail.

1
Add a UNIQUE constraint
ALTER TABLE students ADD CONSTRAINT uq_email UNIQUE (email);
2
Add a FOREIGN KEY
ALTER TABLE orders ADD CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(customer_id) ON DELETE RESTRICT;
3
Add a CHECK constraint
ALTER TABLE employees ADD CONSTRAINT chk_salary CHECK (salary > 0);
4
Drop a constraint by name
ALTER TABLE orders DROP FOREIGN KEY fk_customer;  or  ALTER TABLE students DROP INDEX uq_email;
5
Modify a column to be NOT NULL
ALTER TABLE users MODIFY COLUMN phone VARCHAR(20) NOT NULL; — all existing rows must have a non-NULL phone value first.
SQL — ALTER TABLE constraint management
-- Add UNIQUE to an existing column
ALTER TABLE students
    ADD CONSTRAINT uq_student_email UNIQUE (email);

-- Add a FOREIGN KEY to an existing table
ALTER TABLE orders
    ADD CONSTRAINT fk_orders_cust
        FOREIGN KEY (customer_id)
        REFERENCES  customers(customer_id)
        ON DELETE CASCADE;

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

-- Remove a FOREIGN KEY (must drop FK before dropping column)
ALTER TABLE orders
    DROP FOREIGN KEY fk_orders_cust;

-- Remove a UNIQUE (MySQL treats it as an index)
ALTER TABLE students
    DROP INDEX uq_student_email;

-- Remove a named CHECK constraint (MySQL 8.0+)
ALTER TABLE products
    DROP CHECK chk_price_positive;

-- View all constraints on a table (MySQL)
SELECT *
FROM   information_schema.TABLE_CONSTRAINTS
WHERE  TABLE_NAME = 'orders';

All 6 Constraints in One Table

Here is a single, production-quality table that uses every constraint type — showing how they all work together in a real schema:

SQL — All 6 constraints in one production-ready table
CREATE TABLE enrollments (
    -- ① PRIMARY KEY — auto-increment, unique row identity
    enroll_id   INT              NOT NULL AUTO_INCREMENT,

    -- ② NOT NULL — required fields
    student_id  INT              NOT NULL,
    course_id   INT              NOT NULL,
    course_name VARCHAR(120)     NOT NULL,

    -- ③ UNIQUE — no student can enrol twice in same course
    -- ④ DEFAULT — fallback values for optional fields
    status      ENUM('enrolled','completed','dropped')
                                 NOT NULL DEFAULT 'enrolled',
    score       TINYINT UNSIGNED DEFAULT NULL,
    enrolled_on DATE             NOT NULL DEFAULT (CURRENT_DATE),
    created_at  TIMESTAMP        NOT NULL DEFAULT CURRENT_TIMESTAMP,

    -- Named PRIMARY KEY (① — table level)
    CONSTRAINT pk_enrollments     PRIMARY KEY (enroll_id),

    -- ③ UNIQUE — composite: one enrolment per student per course
    CONSTRAINT uq_student_course  UNIQUE (student_id, course_id),

    -- ⑤ CHECK — score must be 0–100 if provided
    CONSTRAINT chk_score_range    CHECK (score IS NULL OR score BETWEEN 0 AND 100),

    -- ⑥ FOREIGN KEY — links to students and courses tables
    CONSTRAINT fk_enroll_student
        FOREIGN KEY (student_id)
        REFERENCES  students(student_id)
        ON DELETE CASCADE ON UPDATE CASCADE,

    CONSTRAINT fk_enroll_course
        FOREIGN KEY (course_id)
        REFERENCES  courses(course_id)
        ON DELETE RESTRICT ON UPDATE CASCADE
);
📊
Free Resource
Download our SQL Cheat Sheet
All 6 constraints, syntax, and naming conventions — one printable page.
Get Sheet

Try It Yourself

The in-browser runner is pre-loaded with the constraints_demo table — modelling the enrollments table above with real data including valid and boundary-testing values. Query it to see constraints in action.

SQL Query Runner In-Browser
Tables: enrollments · students · courses  ·  Try:
Results will appear here after you run a query
Quick Check — Chapter 9

You delete a customer row from the customers table. The orders table has a FOREIGN KEY on customer_id with ON DELETE CASCADE. What happens to the customer's orders?

Chapter Summary
  • A constraint is a rule enforced by the database engine on every INSERT, UPDATE, and DELETE — any violation is rejected before the data is written.
  • PRIMARY KEY — uniquely identifies each row, implicitly NOT NULL + UNIQUE, only one per table. Use AUTO_INCREMENT for integer PKs. Composite keys (spanning multiple columns) must be declared at table level.
  • NOT NULL — column must always have a value; NULL inserts/updates are rejected. Use this on every required column, and combine with DEFAULT to provide fallbacks.
  • UNIQUE — no two rows may share the same value. Unlike PK, allows NULL and a table can have many. Creates an automatic index. Use for business keys (email, username, SKU).
  • DEFAULT — supplies an automatic value when none is provided at insert time. Accepts literals, functions (CURRENT_TIMESTAMP), and expressions. Ideal for timestamps and status fields.
  • CHECK — validates a boolean condition before storing. Can reference multiple columns. MySQL silently ignored CHECK before v8.0.16 — always verify your version.
  • FOREIGN KEY — links a child table's column to a parent's PRIMARY KEY, enforcing referential integrity. Always create the parent table first. Can't insert a child that references a non-existent parent.
  • Referential actions: RESTRICT (default, safest) prevents parent deletion if children exist · CASCADE propagates deletes/updates · SET NULL nullifies the FK · NO ACTION defers the check.
  • Always name your constraints using CONSTRAINT name TYPE (…) — it makes error messages readable and allows you to ALTER/DROP specific constraints later.
  • Add constraints to existing tables with ALTER TABLE t ADD CONSTRAINT …. Existing data must already satisfy the constraint, or the ALTER fails.
Advertisement
📢Ad space · 728 × 90 · Replace slot ID above

Rate Us on Google

4.8 / 5
Enjoying this tutorial?
Your 5-star review helps other learners discover our free tutorials and encourages us to keep creating quality content.
80+ reviews
Write a Google Review
Join WhatsApp Channel