SQL Notes
Learn SQL Constraints in depth. Explore PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, and DEFAULT constraints that enforce data integrity in relational databases with practical examples.
SQL constraints are guardrails for your data. They enforce rules that keep everything accurate, consistent, and sensible.
Before diving into each constraint type, ensure you are comfortable with SQL Data Types, SQL Operators, and the CREATE TABLE statement.
Types of SQL Constraints
| Constraint | Purpose |
|---|---|
| PRIMARY KEY | Each row has one unique ID |
| 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 one solves a different problem.
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 are mandatory. Try to INSERT without them? Boom—error.
INSERT INTO Students (StudentID)
VALUES (1); -- ERROR: Name and Email cannot be NULLUsernames, emails, phone numbers, dates—stuff you can't live without.
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
);Try to insert a duplicate email:
INSERT INTO Users (UserID, Email)
VALUES (1, 'rahul@example.com');
INSERT INTO Users (UserID, Email)
VALUES (2, 'rahul@example.com'); -- ERROR: Duplicate emailUsernames, emails, product codes—anything that can't repeat.
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
);One per table, max.
Why it matters:
- Quickly locate records
- Build relationships with other tables
- Maintain data consistency
Standard names: id, user_id, order_id, 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 existThis keeps data sane—no dangling references, no orphaned rows.
Check out the Foreign Key Deep Dive for composite keys, ON DELETE, and more.
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 0Min age, score ranges, positive prices—business rules baked in.
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');Status auto-fills 'Pending', CreatedAt auto-fills today.
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't take the same course twice. The combo of StudentID + CourseID is what's unique.
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
Skip NOT NULL on required fields and garbage data sneaks in.
CREATE TABLE Users (
UserID INT PRIMARY KEY,
Email VARCHAR(255) -- Should be NOT NULL
);Overusing CHECK Constraints
Too many CHECK constraints = inserts get complicated. Balance it out.
Forgetting Indexes on Foreign Keys
Index your foreign keys. Joins and cascades will be faster.
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 Complete Guide topic.
Search Terms
sql-complete-guide, sql complete guide, sql, complete, guide, constraints, sql constraints
Related SQL Complete Guide Topics