Databases store large amounts of information, so they must keep that data accurate, consistent, and reliable. Therefore, developers use constraints in SQL to control what kind of data users can insert into a table.
In simple terms, constraints are rules applied to table columns in SQL. These rules restrict invalid data and ensure that the database always contains correct information.
For example, imagine you store customer details in a database. You would not want two customers to have the same Customer ID, or allow an email field to remain empty. In this situation, SQL constraints help enforce these rules automatically.
In this guide, we will learn –
- What constraints in SQL are.
- Why they are important.
- Types of SQL constraints.
- Practical examples and real-world use cases.
What Are Constraints in SQL?
Constraints in SQL are rules that control the type of data allowed in a table column. These rules help maintain data integrity and accuracy inside the database.
Developers usually define constraints while creating a table using the CREATE TABLE statement. However, they can also add constraints later using the ALTER TABLE command.
Simple Example
Imagine you create a table for storing student information.
CREATE TABLE Students (
StudentID INT NOT NULL,
Name VARCHAR(50),
Age INT
);
Here, the NOT NULL constraint ensures that every student must have an ID.
For example:
- StudentID = 101 → valid
- StudentID = NULL → invalid
Therefore, SQL prevents incomplete records from entering the database.
Why Constraints Are Important in SQL
Constraints play an important role in database design because they protect the database from invalid data.
For instance, imagine an online store database. If the database allows duplicate order numbers or empty product IDs, the system will quickly become unreliable.
Because of this, SQL constraints help in several ways:
1- Maintain Data Accuracy
Constraints prevent invalid values from entering a table.
Example: A NOT NULL constraint ensures that every employee record contains an email.
2- Prevent Duplicate Data
Certain constraints ensure uniqueness across records.
Example: A UNIQUE constraint prevents two users from having the same username.
3- Maintain Relationships Between Tables
Some constraints link tables together.
Example: A FOREIGN KEY constraint ensures that an order always belongs to an existing customer.
As a result, the database remains organized and reliable.
Types of Constraints in SQL
SQL provides several built-in constraints that developers use to enforce rules on tables.
The most common constraints in SQL include:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
Now, let’s understand each constraint with simple explanations and examples.
1- NOT NULL Constraint
The NOT NULL constraint ensures that a column cannot contain empty values. In other words, every record must include a value for that column.
Developers usually apply this constraint to important fields like ID, email, or username.
Example
CREATE TABLE Users (
UserID INT NOT NULL,
Username VARCHAR(50) NOT NULL,
Email VARCHAR(100)
);
Here:
- UserID cannot be NULL.
- Username cannot be NULL.
Real-World Example
Let’s imagine a login system.
Every user must have a username and a password. Therefore, developers apply the NOT NULL constraint to prevent empty values.
2- UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different. Therefore, SQL does not allow duplicate values in that column.
However, unlike a primary key, a UNIQUE column may still allow NULL values.
Example
CREATE TABLE Employees (
EmployeeID INT,
Email VARCHAR(100) UNIQUE
);
In this table:
- Each employee must have a different email address.
Real-World Example
Consider a social media platform. Two users cannot register using the same email address. Therefore, developers add a UNIQUE constraint to the email column.
3- PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each row in a table. A table can only have one primary key, and it cannot contain NULL values.
In fact, a primary key automatically combines NOT NULL and UNIQUE constraints.
Example
CREATE TABLE Students (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Age INT
);
Here:
- StudentID uniquely identifies each student.
Real-World Example
Imagine a bank database. Each customer receives a unique Account Number. Therefore, developers use a primary key constraint to prevent duplicate account numbers.
4- FOREIGN KEY Constraint
The FOREIGN KEY constraint creates a relationship between two tables. It ensures that a value in one table matches an existing value in another table.
Because of this, foreign keys maintain referential integrity in databases.
Example
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Here:
- CustomerID in the Orders table must exist in the Customers table.
Real-World Example
Consider an e-commerce website. An order must belong to an existing customer. Therefore, SQL prevents creating an order with a non-existing customer ID.
5- CHECK Constraint
The CHECK constraint ensures that values meet a specific condition before entering the database.
In other words, it validates data based on a rule.
Example
CREATE TABLE Employees (
EmployeeID INT,
Age INT CHECK (Age >= 18)
);
Here:
- SQL only accepts employees who are 18 or older.
Real-World Example
Imagine a job portal database. Applicants must be at least 18 years old. Therefore, a CHECK constraint prevents invalid age values.
6- DEFAULT Constraint
The DEFAULT constraint automatically assigns a value when the user does not provide one.
This feature reduces manual input and keeps data consistent.
Example
CREATE TABLE Orders (
OrderID INT,
Status VARCHAR(20) DEFAULT 'Pending'
);
If a new order does not include a status, SQL automatically sets it to Pending.
Real-World Example
Consider an online order system. When a customer places an order, the status automatically becomes Pending until the system processes it.
Read Also – UPDATE Query in SQL
Rules for Using Constraints in SQL
Although constraints are easy to use, developers follow some best practices while applying them.
Rule 1: Define Constraints During Table Creation
For example:
CREATE TABLE Products (
ProductID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL
);
This approach ensures that SQL enforces rules from the beginning.
Rule 2: Use Constraints to Protect Important Data
For instance:
- Use PRIMARY KEY for unique identifiers
- Use NOT NULL for required fields
- Use CHECK for data validation
Therefore, the database remains consistent.
Rule 3: Avoid Unnecessary Constraints
Although constraints improve data integrity, excessive constraints can reduce flexibility.
For example: A description column does not always need a NOT NULL rule.
Real-World Use Case of SQL Constraints
Let’s consider a simple e-commerce database.
➤ Customers Table
CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
Name VARCHAR(100) NOT NULL,
Email VARCHAR(100) UNIQUE
);
➤ Orders Table
CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
Here’s what happens:
- Each customer has a unique ID.
- Emails cannot repeat.
- Every order belongs to a valid customer.
As a result, the system prevents incorrect data automatically.
Wrapping it Up
Constraints in SQL play a crucial role in maintaining data accuracy, integrity, and reliability. By applying these rules, developers ensure that databases store only valid and meaningful information.
In summary, SQL provides several powerful constraints, including:
- NOT NULL
- UNIQUE
- PRIMARY KEY
- FOREIGN KEY
- CHECK
- DEFAULT
When developers apply these constraints correctly, databases become more structured, secure, and easier to manage.
Read Also – Delete Query in SQL
