Constraints in SQL: Types, Examples, and How to Use Them

Constraints in SQL are rules applied to columns or tables to control the type of data that can be stored in a database.

These rules help enforce data integrity, ensuring that information stored in the database is accurate, valid, and consistent.

*EDUCBA

When a constraint is applied to a column or table, the database management system automatically checks every insert or update operation to ensure the rule is not violated.

For example:

• Prevent duplicate values in a column
• Ensure a column cannot contain NULL values
• Verify that a value falls within a specific range

If a user attempts to insert data that violates a constraint, SQL will reject the operation and return an error.

This automatic enforcement is what makes constraints extremely powerful in maintaining reliable databases.

Database constraints are essential because they protect the integrity and reliability of stored data.

Without proper constraints, databases may contain duplicate records, invalid values, or inconsistent relationships between tables.

Here are some major benefits of using constraints in SQL.

1. Ensures Data Accuracy

Constraints prevent invalid or incorrect data from being inserted into tables.

For example, a check constraint in SQL can ensure that an employee’s salary is always greater than zero.

2. Prevents Duplicate Data

The SQL unique constraint ensures that certain fields, such as email IDs or employee numbers, remain unique.

3. Maintains Relationships Between Tables

Constraints such as foreign keys help maintain relationships between different database tables.

4. Improves Data Consistency

By enforcing predefined rules, constraints ensure that data remains consistent across the database.

5. Reduces Application Errors

When constraints enforce rules at the database level, developers don’t have to rely entirely on application code for validation.

Understanding the type of constraint in SQL is important for designing reliable databases.

SQL provides several built-in constraints that help enforce data integrity.

Below are the most commonly used SQL constraints.

1. NOT NULL Constraint

The NOT NULL constraint ensures that a column cannot store NULL values.

When this constraint is applied, every row must contain a value for that column.

Example

CREATE TABLE Employees (

   EmployeeID INT NOT NULL,

   Name VARCHAR(50) NOT NULL,

   Department VARCHAR(50)

);

In this example:

• EmployeeID cannot be NULL
• Name cannot be NULL
• Department may contain NULL values

This constraint is commonly used for mandatory fields such as IDs or names.

2. UNIQUE Constraint

The SQL unique constraint ensures that all values in a column are different from each other.

It prevents duplicate entries in a column.

This is especially useful for attributes like:

• email addresses
• usernames
• product codes

Example

CREATE TABLE Users (

   UserID INT,

   Email VARCHAR(100) UNIQUE

);

Here:

• Every email address must be unique
• Duplicate emails cannot be inserted

Example of Violation

If the table already contains:

Email: user@example.com

Trying to insert the same email again will produce an error.

3. PRIMARY KEY Constraint

The primary key constraint uniquely identifies each record in a table.

It combines two important rules:

• Values must be unique
• Values cannot be NULL

Each table can have only one primary key.

Example

CREATE TABLE Orders (

   OrderID INT PRIMARY KEY,

   CustomerName VARCHAR(50),

   OrderDate DATE

);

Here:

• OrderID uniquely identifies every order.

Primary keys are fundamental for creating relationships between database tables.

4. FOREIGN KEY Constraint

The foreign key constraint ensures that relationships between tables remain valid.

It links one table to another.

For example:

• A customer table may store customer IDs
• An order table may reference those IDs

Example

CREATE TABLE Orders (

   OrderID INT PRIMARY KEY,

   CustomerID INT,

   FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

);

This ensures that every order refers to an existing customer.

If a customer ID does not exist in the Customers table, SQL will reject the insert.

5. CHECK Constraint

The check constraint in SQL ensures that values meet a specific condition.

This constraint is extremely useful when validating numerical or logical conditions.

Example

CREATE TABLE Employees (

   EmployeeID INT,

   Salary INT CHECK (Salary > 0)

);

In this example:

• Salary must always be greater than zero.

Another Example

CHECK (Age >= 18)

This ensures only adults are entered into the system.

6. DEFAULT Constraint

The DEFAULT constraint assigns a default value to a column when no value is specified.

This is helpful when certain values should automatically be applied.

Example

CREATE TABLE Orders (

   OrderID INT,

   OrderDate DATE DEFAULT CURRENT_DATE

);

If a user inserts a row without specifying OrderDate, SQL will automatically insert the current date.

Constraints are often defined while creating tables using the CREATE TABLE statement.

Example

CREATE TABLE Students (

   StudentID INT PRIMARY KEY,

   Name VARCHAR(50) NOT NULL,

   Email VARCHAR(100) UNIQUE,

   Age INT CHECK (Age >= 18)

);

This table includes multiple constraints in SQL, ensuring:

• Student ID is unique
• Name cannot be NULL
• Email is unique
• Age must be 18 or older

This combination ensures high-quality data.

Constraints can also be added after a table has been created using the ALTER TABLE statement.

Example: Adding UNIQUE Constraint

ALTER TABLE Users

ADD CONSTRAINT unique_email UNIQUE (Email);

Example: Adding CHECK Constraint

ALTER TABLE Employees

ADD CONSTRAINT salary_check

CHECK (Salary > 0);

Using ALTER TABLE allows database administrators to enforce new rules without recreating the table.

Consider a database for an online store.

Table Structure

CREATE TABLE Products (

   ProductID INT PRIMARY KEY,

   ProductName VARCHAR(100) NOT NULL,

   Price DECIMAL CHECK (Price > 0),

   SKU VARCHAR(50) UNIQUE

);

This table uses several types of constraints in SQL:

Together, these constraints ensure the product database remains reliable.

Although constraints are powerful tools, incorrect implementation can cause problems.

Here are some common mistakes developers should avoid.

1. Overusing Constraints

Too many constraints can make database updates more complex.

2. Ignoring Data Validation

Constraints should complement, not replace, validation in application logic.

3. Poor Naming Conventions

When adding constraints, it is good practice to name them clearly.

Example:

CONSTRAINT salary_check

This makes debugging easier.

Constraints in SQL are fundamental tools for maintaining data integrity in relational databases. By defining rules for how data can be stored, constraints ensure that databases remain accurate, consistent, and reliable.

Understanding the type of constraint in SQL—including primary keys, foreign keys, the SQL unique constraint, and the check constraint in SQL—is essential for anyone working with databases.

When implemented correctly, constraints help:

• prevent invalid data
• enforce relationships between tables
• maintain consistency across large datasets

Whether designing a small application database or managing large enterprise systems, using SQL constraints effectively is a critical skill for developers, analysts, and data engineers.