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 KEYPK
Uniquely identifies every row. Implicitly NOT NULL + UNIQUE. One per table.
NOT NULLNN
Column must always have a value. NULL is rejected.
UNIQUEUQ
No two rows may share the same value. NULL is allowed.
DEFAULTDF
Fallback value used automatically when nothing is provided.
CHECKCK
Validates a condition before storing. Rejects values that fail.
FOREIGN KEYFK
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
Uniqueness + Not Null + Row Identity
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 INTNOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)NOT NULL
);
-- ② Table-level (preferred — allows naming)CREATE TABLE students (
student_id INTNOT 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 INTNOT NULL,
product_id INTNOT NULL,
quantity INTNOT 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
Required Value · No Blanks Allowed
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 INTAUTO_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 NULLDEFAULT'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 NULLINSERT 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
No Duplicates · Allows NULL
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 INTAUTO_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 uniqueCONSTRAINT 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
Automatic Fallback Values
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
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 INTAUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)NOT NULL,
-- Range check: age must be between 5 and 100
age TINYINT UNSIGNEDCHECK (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 DATENOT 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
Links Tables · Referential Integrity
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
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 postsCREATE TABLE posts (
post_id INTAUTO_INCREMENT PRIMARY KEY,
user_id INTNOT NULL,
title VARCHAR(200)NOT NULL,
CONSTRAINT fk_post_user
FOREIGN KEY (user_id)
REFERENCES users(user_id)
ON DELETE CASCADEON UPDATE CASCADE
);
-- ON DELETE SET NULL: department deleted → employee.dept_id becomes NULLCREATE TABLE employees (
emp_id INTAUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100)NOT NULL,
dept_id INT, -- NULL allowed so SET NULL worksCONSTRAINT 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.
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 columnALTER TABLE students
ADD CONSTRAINT uq_student_email UNIQUE (email);
-- Add a FOREIGN KEY to an existing tableALTER TABLE orders
ADD CONSTRAINT fk_orders_cust
FOREIGN KEY (customer_id)
REFERENCES customers(customer_id)
ON DELETE CASCADE;
-- Add a CHECK constraintALTER 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 INTNOT NULL AUTO_INCREMENT,
-- ② NOT NULL — required fields
student_id INTNOT NULL,
course_id INTNOT 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 UNSIGNEDDEFAULT NULL,
enrolled_on DATENOT NULL DEFAULT (CURRENT_DATE),
created_at TIMESTAMPNOT NULL DEFAULT CURRENT_TIMESTAMP,
-- Named PRIMARY KEY (① — table level)CONSTRAINT pk_enrollments PRIMARY KEY (enroll_id),
-- ③ UNIQUE — composite: one enrolment per student per courseCONSTRAINT uq_student_course UNIQUE (student_id, course_id),
-- ⑤ CHECK — score must be 0–100 if providedCONSTRAINT chk_score_range CHECK (score IS NULL OR score BETWEEN 0 AND 100),
-- ⑥ FOREIGN KEY — links to students and courses tablesCONSTRAINT 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.
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.