SQL Constraints: An In-depth Tutorial Everything You Need to Know – SQL constraints help maintain data consistency, preventing invalid data entries and optimizing query performance.

SQL Constraints:

SQL Constraints are used to limit the type of data that can go into a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action, the action is aborted.

What are SQL constraints?

SQL constraints are rules applied to table columns to control the type, range, and validity of data stored in a database. They help maintain data integrity and accuracy.

Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The purpose of constraints is to maintain the data integrity during an update/delete/insert into a table. Once the constraint is placed, if any operation in the database does not follow the rules specified by the constraint, the particular operation is aborted.

Types of SQL Constraints:

SQL constraints can be at a column or a table level. Column level constraints apply to specific columns in a table and do not specify a column name except the check constraints. They refer to the column that they follow. The names are specified by the Table-level constraints of the columns to which they apply.

Column-Level SQL Constraints:

These constraints are applied directly to a single column during column definition.

Characteristics

Defined along with the column
Apply to only one column
Simpler and commonly used

Common Column-Level Constraints

NOT NULL
UNIQUE
PRIMARY KEY
DEFAULT
CHECK
FOREIGN KEY (single column)

Example (Column-Level Constraints)

CREATE TABLE Employees (
emp_id INT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) UNIQUE,
salary DECIMAL(10,2) CHECK (salary > 0),
dept_id INT REFERENCES Departments(dept_id)
);

Table-Level SQL Constraints:

These constraints are defined after all columns inside the table.

Characteristics

Applied at the table level
Can involve multiple columns
Required for composite keys

Common Table-Level Constraints

PRIMARY KEY
FOREIGN KEY
UNIQUE
CHECK (multi-column)
COMPOSITE KEY

Example (Table-Level Constraints)
CREATE TABLE Employees (
emp_id INT,
name VARCHAR(50),
email VARCHAR(100),
salary DECIMAL(10,2),
dept_id INT,

CONSTRAINT pk_emp PRIMARY KEY (emp_id),
CONSTRAINT uq_email UNIQUE (email),
CONSTRAINT chk_salary CHECK (salary > 0),
CONSTRAINT fk_dept FOREIGN KEY (dept_id)
REFERENCES Departments(dept_id)
);

When to Use What?

Use column-level for simple rules
Use table-level for relationships and multi-column rules

SQL NOT NULL Constraint:

The NOT NULL constraint ensures that a column cannot contain NULL values.
It forces every record to have a valid value for that column.

Prevents empty or missing values
Ensures mandatory fields are always filled
Improves data accuracy and integrity
Commonly used for primary fields like names, IDs, emails
A column with NOT NULL must have a value during INSERT

In MySQL, the ENGINE option specifies the storage engine (like InnoDB, MyISAM).
The NOT NULL constraint works independently of the engine, but InnoDB is recommended for data integrity.

CREATE TABLE employees (
emp_id INT NOT NULL,
emp_name VARCHAR(50) NOT NULL,
email VARCHAR(100),
salary DECIMAL(10,2) NOT NULL
)
ENGINE = InnoDB;

emp_id INT NOT NULL → emp_id cannot be NULL
emp_name VARCHAR(50) NOT NULL → employee name is mandatory
salary DECIMAL(10,2) NOT NULL → salary must be provided
ENGINE = InnoDB → uses InnoDB storage engine (supports constraints & transactions)

NOT NULL works in all engines, but foreign keys require InnoDB.

Altering a NOT NULL Constraint after the Creation of a Table:

Consider, that after the creation of the ‘Sales’ table and storing information in the same, business logic changes and now we’re instructed that no sales can be recorded in the ‘Sales’ table without recording the amount of profit that was earned on the sale. In that case, we will now have to add a constraint that the profit column cannot be null. This is how we would do it:

ALTER TABLE Sales
MODIFY Profit int NOT NULL;

SQL UNIQUE Constraint:

The UNIQUE constraint ensures that all values in a column (or group of columns) are different.
It prevents duplicate values, while still allowing NULL values (database-dependent).

Prevents duplicate values in a column
Helps maintain data integrity
A table can have multiple UNIQUE constraints
Allows NULL values (MySQL allows multiple NULLs)
Automatically creates a unique index

Syntax (Column-Level)

Syntax (Table-Level)

UNIQUE with Multiple Columns (Composite UNIQUE)

CREATE TABLE enrollments (
student_id INT,
course_id INT,
CONSTRAINT uq_student_course UNIQUE (student_id, course_id) );

UNIQUE Constraint with ENGINE Option (MySQL)

Dropping a UNIQUE Constraint:

To drop a UNIQUE constraint, we will need to specify the naming convention that was used during the creation of the constraint:

MySQL:

ALTER TABLE Sales
DROP INDEX UC_Sales;

SQL Server / Oracle / MS Access:

ALTER TABLE Sales
DROP CONSTRAINT UC_Sales;

Altering a UNIQUE Constraint after the Creation of a Table:

To create a UNIQUE constraint on the “Sale_Id” column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Sales
ADD UNIQUE(Sale_Id);

SQL PRIMARY KEY Constraint:

A PRIMARY KEY uniquely identifies each record in a table.

It does not allow NULL values and does not allow duplicates.

Ensures unique identification of rows
Combines UNIQUE + NOT NULL
Only one PRIMARY KEY per table
Can be single-column or composite
Automatically creates a unique index
Best used with InnoDB engine in MySQL

Column-Level PRIMARY KEY Constraint

Syntax

Explanation

emp_id uniquely identifies each employee
PRIMARY KEY defined inside the column definition
ENGINE = InnoDB ensures integrity and FK support

Table-Level PRIMARY KEY Constraint

Syntax

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
emp_id INT,
emp_name VARCHAR(50) NOT NULL,
email VARCHAR(100),
salary DECIMAL(10,2),
CONSTRAINT pk_emp PRIMARY KEY (emp_id) )
ENGINE = InnoDB;

Explanation

PRIMARY KEY defined after all columns
Allows constraint naming
Required for composite primary keys

Composite PRIMARY KEY (Table-Level Only)

A student cannot enroll in the same course twice.

PRIMARY KEY with AUTO_INCREMENT (MySQL)

DROP TABLE IF EXISTS employees;
CREATE TABLE employees (
emp_id INT AUTO_INCREMENT PRIMARY KEY,
emp_name VARCHAR(50) NOT NULL,
email VARCHAR(100) )
ENGINE = InnoDB;

SQL FOREIGN KEY Constraint:

The foreign key constraint is used to prevent operations in a relational database that would destroy links between tables. The FOREIGN KEY is a column (or a group of columns) in one table, that refers to the PRIMARY KEY of another table. The table with the FOREIGN KEY is called the child table while the referenced table with the PRIMARY KEY is called the parent table.

Consider the two following tables:

Sales table:

Sale_Id	Sale_Amount	Vendor_Name	Sale_Date	Profit
123	100	ABC	01-12-2026	20
234	200	BCD	14-06-2026	55
345	500	CDE	22-03-2026	32
456	100	EFG	25-04-2026	40

Sales_Person table:

Sales_Person_Id	Sales_Person_Name	Sales_Person_Location	Sale_Id
1	Jac	Texas	234
2	Sweety	Dubai	456
3	Jim	Arizona	123
4	Rose	Canada	345

The ‘Sale_Id’ column in the ‘Sales_person’ table refers to the ‘Sale_Id’ in the ‘Sales’ table.

The ‘Sale_Id’ in the ‘Sales’ table is the PRIMARY KEY.

The ‘Sale_Id’ in the ‘Sales_Person’ table is the FOREIGN KEY.

Notice that every value for ‘Sale_Id’ present in the ‘Sales_Person’ table is also available in the ‘Sale_Id’ column in the ‘Sales’ table. This is so due to the PRIMARY KEY – FOREIGN KEY relationship defined between the two tables. No value for a ‘Sale_Id’ can be entered into the ‘Sales_Person’ table that does not already exist in the ‘Sales’ table. If we try to insert such a value, because of the PRIMARY KEY – FOREIGN KEY constraint, the insertion will be rejected.

Applying the FOREIGN KEY Constraint:

The FOREIGN KEY constraint can be defined either during the creation of the table or can be put in place later via an alter statement.

Declaring a FOREIGN KEY Constraint during the Creation of a Table:

The following SQL creates a FOREIGN KEY on the ‘Sale_Id’ column when the ‘Sales_Person’ table is created:

MySQL:

CREATE TABLE Sales_Person (

Sales_Person_Id int NOT NULL,

Sales_Person_Name varchar(255),

Sales_Person_Location varchar(255),

Sale_Id int NOT NULL,

PRIMARY KEY (Sales_Person_Id),

FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id)

);

SQL Server / Oracle / MS Access:

CREATE TABLE Sales_Person (

Sales_Person_Id int NOT NULL PRIMARY KEY,

Sales_Person_Name varchar(255),

Sales_Person_Location varchar(255),

Sale_Id int FOREIGN KEY REFERENCES Sales(Sale_Id)

);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Sales_Person (
Sales_Person_Id int NOT NULL,
Sales_Person_Name varchar(255),
Sales_Person_Location varchar(255),
Sale_Id int NOT NULL,
PRIMARY KEY (Sales_Person_Id),
CONSTRAINT FK_Sales_Sales_Person FOREIGN KEY (Sale_Id)
REFERENCES Persons Sales(Sale_Id)
);

Altering a FOREIGN KEY Constraint after the Creation of a Table:

To create a FOREIGN KEY constraint on the ‘Sale_Id’ column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Sales_Person
ADD FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id);

To allow naming of a FOREIGN KEY constraint and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Sales_Person
ADD CONSTRAINT FK_Sales_Sales_Person
FOREIGN KEY (Sale_Id) REFERENCES Sales(Sale_Id);

Dropping a FOREIGN KEY Constraint:

To drop a FOREIGN KEY constraint, use the following SQL:

MySQL:

ALTER TABLE Sales_Person
DROP FOREIGN KEY FK_Sales_Sales_Person;

SQL Server / Oracle / MS Access:

ALTER TABLE Sales_Person
DROP CONSTRAINT FK_Sales_Sales_Person;

SQL CHECK Constraint:

The CHECK constraint allows you to specify a condition that needs to be met to insert data into a table successfully.

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int NOT NULL CHECK (AGE >= 18)
);

Altering a CHECK Constraint after the Creation of a Table:

To create a CHECK constraint on the ‘Vendor_Name’ column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Sales
ADD CHECK (Vendor_Name<> ‘XYZ’);

Altering a PRIMARY KEY Constraint after the Creation of a Table:To create a PRIMARY KEY constraint on the ‘Sale_Id’ column when the table is already created, use the following SQL:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Sales
ADD PRIMARY KEY (Sale_Id);

SQL DEFAULT Constraint:

The DEFAULT constraint provides a default value for a column when no value is specified during insertion. This is useful for ensuring that certain columns always have a meaningful value, even if the user does not provide one

Example:

CREATE TABLE Student
(
ID int(6) NOT NULL,
NAME varchar(10) NOT NULL,
AGE int DEFAULT 18
);

Explanation: Here, if no value is provided for AGE during an insert, the default value of 18 will be assigned automatically.

SQL CREATE INDEX Statement:

The CREATE INDEX constraint in SQL helps in retrieving the results more quickly. It is like a book’s index. Like the book’s index helps us find a chapter soon, the table’s index allows the system to retrieve results quickly. The CREATE INDEX constraint is not visible or of any use to the user; it is only helpful to the backend or the system. The syntax for creating an index is:

Indexes are used to retrieve data from the database more quickly than otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.

CREATE INDEX Syntax

Creates an index on a table. Duplicate values are allowed:

CREATE INDEXindex_name
ONtable_name (column1, column2, …);

CREATE UNIQUE INDEX Syntax

Creates a unique index on a table. Duplicate values are not allowed:

CREATE UNIQUE INDEXindex_name
ONtable_name (column1, column2, …);

Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.

CREATE INDEX ExampleThe SQL statement below creates an index named “idx_lastname” on the “LastName” column in the “Persons” table:

CREATE INDEX idx_lastname
ON Persons (LastName);

If you want to create an index on a combination of columns, you can list the column names within the parentheses, separated by commas:

CREATE INDEX idx_pname
ON Persons (LastName, FirstName);

Constraints in SQL ensure data integrity, accuracy, and reliability by imposing specific rules on database tables.
They are essential for database administration, allowing for the enforcement of unique values, non-null requirements, and referential integrity.
Constraints in SQL can be applied both during and after table creation, offering flexibility in database design and management.
Key SQL constraints include NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT, and CREATE INDEX, each serving a distinct purpose in data validation and optimization.