SQL Constraints 

When working on relational databases, you need to maintain the consistency and quality of data. To have a proper database, you need to follow constraints that will create a table with certain conditions of SQL constraints. The SQL constraint is a rule used to maintain accuracy and integrity. There are some types of constraints in SQL that can be used based on the types of applications you are creating. In this article, you will gain knowledge on SQL Constraints and their functionality in detail with examples.

Table of Contents:

• What Are SQL Constraints?
• Importance of SQL Constraints
• Types of SQL Constraints
  1. NOT NULL in SQL
  2. UNIQUE Constraint in SQL
  3. PRIMARY KEY Constraint in SQL
  4. FOREIGN KEY Constraint in SQL
  5. CHECK Constraint in SQL
  6. DEFAULT Constraint in SQL
• Performance Considerations of Constraints in SQL
• Real-World Examples
• Errors and Troubleshooting in SQL Constraints
• Conclusion

What Are SQL Constraints?

SQL Constraints are the rules that restrict users from inserting the type of data into table columns. The Constraint rules are applied on the database level, which means that you cannot insert or update data types that do not follow the rules. Following the SQL Constraints will reduce the risk of errors and inconsistencies. 

Importance of SQL Constraints

SQL constraints are the most important rules that a developer or user needs to follow. Without these constraints, the data will become inconsistent, and you will have to compromise on data integrity, which may lead to duplicates. For example, if two users use the same mobile number, as the mobile number is a unique identifier, this constraint will make sure that it is not repeated. 

Major importance of SQL Constraints:

• It creates a relationship between the tables in a database. 
• Maintains consistency in the tables.
• It will prevent NULL values and duplicates. 
• It has predefined rules for the tables. 

Learn SQL from Scratch: Build a Rock-Solid Data Foundation

Unlock the power of databases with hands-on SQL training and become a data-driven professional.

Explore Program

Types of SQL Constraints

There are six main types of constraints in SQL used every day while creating an application. 

1. NOT NULL in SQL 

The NOT NULL constraint in SQL is used to check and prevent you from providing NULL values in the columns. If the column has a NOT NULL constraint, then that table shouldn’t have NULL values or empty values in any of the rows.

Why Do We Need to Use a NOT NULL Constraint?

1. To make sure that all the objects have some values in the table.  
2. The NOT NULL constraint will make sure all the variables have values so that there is no third-person interference.
3. By following these rules, it will be easier to identify the data and also create a unique identity, for example, giving employees their ID number and name. 

Syntax:

column_name data_type NOT NULL

Example:

CREATE TABLE Students (
  ID INT NOT NULL,
  Name VARCHAR(10) NOT NULL,
  Course VARCHAR(20),
  JoinDate DATE NOT NULL
);
INSERT INTO Students (ID, Name, Course, JoinDate)
VALUES (1, 'Karna', 'Finance', '2023-06-15');
SELECT * FROM Students;

Output:

Explanation: Here, the NOT NULL constraint has been used in the JoinDate data, as it shouldn’t be empty, which may lead to a compromise in data integrity. 

2. UNIQUE Constraint in SQL

The UNIQUE Constraint in SQL is a rule that maintains data integrity by not allowing any duplicate values in a row. This can be used in a single row or multiple rows at a time. 

Why Do We Need To Use a UNIQUE Constraint?

1. It ensures that no two rows should have the same values in the constraint column. 
2. It maintains the integrity of data by preserving the unique values. For example, phone numbers, employee ID, and usernames. 
3. It makes the search for the data easier, as every object has unique values. 
4. It prevents bugs or errors caused by duplicate values. 

Syntax:

column_name data_type UNIQUE

Example:

CREATE TABLE People (
  Username VARCHAR(20) UNIQUE,
  Email VARCHAR(50) UNIQUE
);
INSERT INTO People (Username, Email)
VALUES ('karna123', '[email protected]');
SELECT * FROM People;

Output:

For multiple Columns:

Syntax:

For multiple columns (composite unique):
UNIQUE (column1, column2)

Example:

CREATE TABLE PeopleLogins (
  Username VARCHAR(20),
  LoginDate DATE,
  UNIQUE (Username, LoginDate)
);
INSERT INTO PeopleLogins (Username, LoginDate)
VALUES ('karna123', '2025-05-12');
SELECT * FROM PeopleLogins;

Output:

Explanation: Here, the unique constraint has been used in both the username and login date rows in a single command. 

3. PRIMARY KEY Constraint in SQL

A primary Key constraint in SQL is used in a column or multiple columns in a schema. A primary key helps in identifying each record in a table. It automatically establishes not null and unique by default if you forgot to implement it. 

Why Do We Need a Primary Key Constraint?

1. It helps to uniquely identify each record.
2. There shouldn’t be two of the same primary key values in a row.
3. Same as NOT NULL, a Primary key also doesn’t support empty values. 
4. The primary key helps the foreign key in building a relationship between two tables. 

Syntax:

CREATE TABLE TableName (
    Column1 DataType PRIMARY KEY,
    Column2 DataType,
    ...
);

Example:

CREATE TABLE Customers (
    CustomerID INT PRIMARY KEY, 
    FullName VARCHAR(20),
    Email VARCHAR(20) UNIQUE, 
    DateOfBirth DATE
);
INSERT INTO Customers (CustomerID, FullName, Email, DateOfBirth)
VALUES (1, 'Priya', '[email protected]', '1985-10-15'),
       (2, 'Lavanya', '[email protected]', '1990-02-20'),
       (3, 'Tarun', '[email protected]', '1992-08-05');
SELECT * FROM Customers;

Output:

Explanation: Here, each customer and their email have a unique ID. 

4. FOREIGN KEY Constraint in SQL

A foreign key constraint in SQL is used to create a relationship between two tables. It works by referring to the primary key of another table. The primary key uniquely identifies each record in a table, while the foreign key is used in a different table to refer to that unique ID.

Why Do We Need To Use a Foreign Key Constraint?

1. It maintains the integrity and consistency by combining tables based on their common values. 
2. It prevents inserting the data that doesn’t match the primary key, and doesn’t have any reference value in it. 
3. It will raise an error if you use INSERT or UPDATE and don’t find the matching parent record in the table.

Syntax:

FOREIGN KEY (child_column) REFERENCES parent_table(parent_column)

Example:

CREATE TABLE Customers (
  CustomerID INT PRIMARY KEY,
  Name VARCHAR(20),
  Email VARCHAR(20)
);
INSERT INTO Customers (CustomerID, Name, Email)
VALUES (101, 'Arun', '[email protected]');
CREATE TABLE Orders (
  OrderID INT PRIMARY KEY,
  OrderDate DATE,
  Amount DECIMAL(10, 2),
  CustomerID INT,
  FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);
INSERT INTO Orders (OrderID, OrderDate, Amount, CustomerID)
VALUES (1, '2025-04-22', 250.00, 101);
SELECT * FROM orders;

Output:

Explanation: Here, the foreign key merged the orders and customers tables, as both tables consist of the same Customer ID.

5. CHECK Constraint in SQL

The CHECK Constraint in SQL will only insert the values that follow the specific condition of the constraint. It will reject or not execute any values if the specific condition is not met. The CHECK constraint will work only in the MySQL software that came after version 8.0.16.

There are two ways to add a CHECK constraint:

• Inline 
• Table-level constraint 

Why Do We Need To Use a CHECK Constraint? 

1. To establish certain rules in the database.
2. This will evaluate the values before executing them.
3. It will make sure there is no loss of data and maintain the consistency of data. 

Syntax for inline constraint:

column_name datatype CHECK (condition)

Syntax for table-level constraint:

CREATE TABLE table_name (
  ...,
  CHECK (condition)
);

Example for inline: 

CREATE TABLE ProductsInline (
  ProductID INT PRIMARY KEY,
  Name VARCHAR(20),
--Inline command
  Price DECIMAL(10,2) CHECK (Price > 0) 
);
INSERT INTO ProductsInline (ProductID, Name, Price)
VALUES (1, 'Cellphone', 12000.00);
SELECT * FROM ProductsInline;

Output: 

Explanation: Here, the inline checks the price of the product and fetches the output if it is greater than 0. 

Example for table-level Check

CREATE TABLE ProductsTableLevel (
  ProductID INT,
  Name VARCHAR(20),
  Price DECIMAL(10,2),
--table-level check
  CONSTRAINT chk_price_positive CHECK (Price > 0), 
  PRIMARY KEY (ProductID)
);
INSERT INTO ProductsTableLevel (ProductID, Name, Price)
VALUES (1, 'Graphics card', 134000.00);
SELECT * FROM ProductsTableLevel;

Output:

Explanation: Here, the Table-level check verified the price and gave output as the price is greater than 0. 

6. DEFAULT Constraint in SQL

The DEFAULT Constraint in SQL will automatically assign a default value to the row when you don’t mention it in the query, or while using the INSERT operation. This will only be used when you didn’t specify the value. If you specify, then the value will be executed. 

Why Do We Need To Use SQL DEFAULT Constraint?

1.  It maintains the consistency by applying values by default wherever necessary.
2. It will insert values automatically into certain rows, which are repeated, so that you do not need to update them every time. 
3. It reduces the NULL values in a column as it has default values for that particular record. 

Syntax:

CREATE TABLE TableName (
    Column1 DataType DEFAULT default_value,
    Column2 DataType,
    ...
);

Example:

CREATE TABLE Accounts (
  AccountID INT, 
  CreatedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO Accounts (AccountID) 
VALUES (1);
INSERT INTO Accounts (AccountID) 
VALUES (2);
SELECT * FROM Accounts;

Output:

Explanation: Here, the creation time is not mentioned in the query, but the account creation time has been printed with the help of the DEFAULT constraint. 

Performance Considerations of Constraints in SQL

Real-World Examples 

Case 1: To get the book publisher name that follows all the constraint rules. 

Example:

CREATE TABLE publishers (
    publisher_id INT PRIMARY KEY,                       
    name VARCHAR(100) UNIQUE NOT NULL,                  
    state_code CHAR(2) DEFAULT 'US',                 
    established_year INT CHECK (established_year >= 1440) 
);
INSERT INTO publishers (publisher_id, name, state_code, established_year)
VALUES 
(1, 'KTC', 'JK', 1935),
(2, 'Polimer', 'UP', 1989),
(3, 'Dinakaran', 'KL', 2596),
(4, 'ABC', 'PB', 1882),
(5, 'Vikatan', 'TN', 1634);
SELECT * FROM publishers; 

Output:

Explanation: Here, the NOT NULL made sure that there are no NULL values, and the CHECK constraint checks that all the published years are greater than 1440. 

Case 2: To get the title and details of the films. 

Example:

CREATE TABLE directors (
    director_id INT PRIMARY KEY,                         
    full_name VARCHAR(20) NOT NULL,                     
    email VARCHAR(20) UNIQUE NOT NULL,                  
    nationality_code CHAR(2) DEFAULT 'US',               
    birth_year INT CHECK (birth_year >= 1900)           
);
CREATE TABLE films (
    film_id INT PRIMARY KEY,                             
    title VARCHAR(20) NOT NULL,                         
    director_id INT,                                      
    rating DECIMAL(3,1) CHECK (rating BETWEEN 0 AND 10), 
    genre VARCHAR(20) DEFAULT 'Drama',
    FOREIGN KEY (director_id) REFERENCES directors(director_id)
);
INSERT INTO directors (director_id, full_name, email, nationality_code, birth_year)
VALUES
(1, 'A', '[email protected]', 'PB', 1970),
(2, 'B', '[email protected]', 'KA', 1983),
(3, 'O', '[email protected]', 'AP', 1969),
(4, 'D', '[email protected]', 'KL', 1967),
(5, 'H', '[email protected]', 'IN', 1941);
INSERT INTO films (film_id, title, director_id, rating, genre)
VALUES
(101, 'Iron Man', 1, 8.8, 'Sci-Fi'),
(102, 'Bad Guy', 2, 7.8, 'Drama'),
(103, 'Pink', 3, 8.6, 'Thriller'),
(104, 'Tik tik tik', 4, 8.2, 'Sci-Fi'),
(105, 'Perusu', 5, 8.6, 'Animation');
SELECT * FROM films;

Output:

Explanation: Here, the foreign key merged the director_id from the directors table and films table with the help of the reference key constraint.

Errors and Troubleshooting in SQL Constraints

Primary key violation: 

• If there is any duplicate primary key constraint in the same database, it will cause an error. 
• Use a SELECT statement to check whether the primary key exists or not. If it is, either delete it or create another Primary key. 

Foreign key violation:

• When the foreign key does not match the parent table.
• Use REFERENCE KEY or query like ON DELETE SET NULL if needed. 

Default constraint violation:

• When the default values don’t apply, or any unexpected null value appears in the table or row.
• Make sure you don’t apply NULL values or define DEFAULT constraints in the row. 

SQL Simplified: Learn for Free, Grow for Life

Master the language of data and start your journey toward a data-centric career—without spending a dime!

Explore Program

Conclusion

SQL Constraints are a very important tool in databases that help in maintaining consistency, integrity, and preventing duplicate values in a table. By applying these rules, the database can be improved without any errors or decrease in the quality of the query. These rules are applied at the database level. To have a clearer and more secure database, you have to follow all the rules of constraints. In this blog, you’ve learned about the six types of constraints in SQL, why they are used, and how they function within a database.

Take your skills to the next level by enrolling in the SQL Training Course today and gaining hands-on experience. Also, prepare for job interviews with SQL Interview Questions, prepared by industry experts.

SQL Constraints – FAQs