SQL Topics
SQL Constraints
title: SQL Constraints
SQL constraints enforce rules on data columns to maintain accuracy, consistency, and integrity in relational databases.
Before diving into each constraint type, ensure you are comfortable with SQL Data Types, SQL Operators, and the CREATE TABLE statement.
Why Constraints Matter
Every database stores information that other parts of an application rely on. A missing or malformed value can corrupt reports, break business logic, and cause financial or operational losses.
Constraints act as guardrails. They reject invalid data before it reaches storage.
Consider a school database. Two students with the same roll number, an enrollment referencing a non-existent student, or a negative score stored as a grade—all are preventable with proper constraints.
Types of SQL Constraints
| Constraint | Purpose |
|---|---|
| PRIMARY KEY | Uniquely identifies each row |
| FOREIGN KEY | Links two tables together |
| UNIQUE | Ensures all values in a column are distinct |
| NOT NULL | Prevents NULL values in a column |
| CHECK | Enforces a specific condition |
| DEFAULT | Assigns a default value when none is provided |
Each constraint serves a specific role in enforcing database rules.
NOT NULL Constraint
By default, columns can store NULL. The NOT NULL constraint prevents that.
CREATE TABLE Students (
StudentID INT,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(255) NOT NULL
);Name and Email must always contain values. Inserting a record without them will fail.
INSERT INTO Students (StudentID)
VALUES (1); -- ERROR: Name and Email cannot be NULLNOT NULL is essential for fields like usernames, emails, phone numbers, and order dates.
UNIQUE Constraint
The UNIQUE constraint ensures all values in a column are distinct.
A table can have multiple UNIQUE columns, unlike PRIMARY KEY, which is limited to one per table.
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(255) UNIQUE,
Phone VARCHAR(20) UNIQUE
);Attempting to insert a duplicate email fails:
INSERT INTO Users (UserID, Email)
VALUES (1, 'rahul@example.com');
INSERT INTO Users (UserID, Email)
VALUES (2, 'rahul@example.com'); -- ERROR: Duplicate emailUNIQUE is ideal for usernames, email addresses, and product codes.
PRIMARY KEY Constraint
A PRIMARY KEY uniquely identifies each row in a table. It automatically combines UNIQUE and NOT NULL.
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL
);A table can have only one PRIMARY KEY.
A primary key helps databases:
- Quickly locate records
- Build relationships with other tables
- Maintain data consistency
Common primary key columns include id, user_id, order_id, and employee_id.
INSERT INTO Students (StudentID, Name)
VALUES (1, 'Rahul'); -- Valid
INSERT INTO Students (StudentID, Name)
VALUES (1, 'Priya'); -- ERROR: Duplicate IDFOREIGN KEY Constraint
A FOREIGN KEY links a column in one table to the PRIMARY KEY of another.
This creates a relationship between two tables and prevents references to non-existent records.
CREATE TABLE Departments (
DeptID INT PRIMARY KEY,
DeptName VARCHAR(100)
);
CREATE TABLE Employees (
EmpID INT PRIMARY KEY,
Name VARCHAR(100),
DeptID INT,
FOREIGN KEY (DeptID)
REFERENCES Departments(DeptID)
);INSERT INTO Employees (EmpID, Name, DeptID)
VALUES (1, 'Amit', 10); -- ERROR if DeptID 10 does not existForeign keys maintain referential integrity and prevent orphan records.
See Foreign Key Deep Dive for composite keys, ON DELETE actions, and best practices.
CHECK Constraint
The CHECK constraint validates data against a condition before insertion.
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Price DECIMAL(10,2) CHECK (Price > 0),
Quantity INT CHECK (Quantity >= 0)
);INSERT INTO Products (ProductID, Price, Quantity)
VALUES (1, -50, 10); -- ERROR: Price must be greater than 0CHECK is useful for enforcing business rules such as minimum age, valid score ranges, or positive prices.
DEFAULT Constraint
The DEFAULT constraint assigns a predefined value when no value is specified during insertion.
CREATE TABLE Tasks (
TaskID INT PRIMARY KEY,
Title VARCHAR(255),
Status VARCHAR(20) DEFAULT 'Pending',
CreatedAt DATE DEFAULT CURRENT_DATE
);INSERT INTO Tasks (TaskID, Title)
VALUES (1, 'Review Database Schema');The Status becomes 'Pending' and CreatedAt becomes today's date automatically.
DEFAULT reduces manual input and ensures consistent values.
Composite Key Constraint
Sometimes a single column is not enough to uniquely identify a row. A composite key uses two or more columns together.
CREATE TABLE Enrollments (
StudentID INT,
CourseID INT,
EnrollmentDate DATE,
PRIMARY KEY (StudentID, CourseID),
FOREIGN KEY (StudentID) REFERENCES Students(StudentID),
FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
);A student can enroll in the same course only once. The combination of StudentID and CourseID enforces this.
Explore Composite Keys for advanced composite key patterns and use cases.
Multiple Constraints Together
Professional schemas combine several constraints on a single table.
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT NOT NULL,
OrderDate DATE DEFAULT CURRENT_DATE,
Status VARCHAR(20) DEFAULT 'Processing',
Amount DECIMAL(10,2) CHECK (Amount > 0),
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);This example enforces uniqueness, referential integrity, validation, and default values simultaneously.
Common Mistakes
Missing NOT NULL on Required Columns
Leaving required columns nullable allows empty data to enter the database unintentionally.
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(255) -- Should be NOT NULL
);Overusing CHECK Constraints
Excessive CHECK constraints can complicate inserts and migrations. Balance validation with practicality.
Forgetting Indexes on Foreign Keys
Foreign key columns should be indexed to maintain join performance at scale.
Best Practices
| Practice | Reason |
|---|---|
| Always use a PRIMARY KEY | Enables reliable record identification |
| Use NOT NULL for required fields | Prevents incomplete records |
| Add UNIQUE to email or username columns | Avoids duplicate accounts |
| Use FOREIGN KEY for relationships | Maintains data consistency |
| Apply CHECK for business rules | Keeps invalid data out |
| Set useful DEFAULT values | Reduces boilerplate in INSERT statements |
Summary
SQL constraints ensure data remains accurate, consistent, and meaningful. The six most common constraints—NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT—form the backbone of reliable database design.
Use them together to build schemas that enforce real-world business rules automatically.
Next Step
Learn about Unique Key Constraints in detail, or return to the SQL Basics overview.
Exam Focus
Revise definitions, diagrams, examples, and short-answer points for SQL Constraints.
Interview Use
Prepare one clear explanation, one practical example, and one common mistake for this SQL topic.
Search Terms
sql, sql complete guide, sql tutorial, sql notes, complete, guide, constraints, sql constraints
Related SQL Topics