Constraints in SQL

DevSniper
Published : 03 January 2025

145 Views

In SQL, constraints are rules applied to table column(s) or entire tables to enforce data integrity and reliability. Constraints are essential for defining how data should be stored and retrieved. Constraints help prevent invalid data entry and maintain the consistency and accuracy of the data.

In this article, we will learn about constraints , it's type , use cases and so on. By the end, you will have a clear understanding about constraints , it's use and importance , syntax and many more things in depth

Syntax

1. Adding Constraints at the time of table creation

CREATE TABLE TableName(
   column1 datatype constraint,
   column2 datatype constraint,
   ....
   columnN datatype constraint
);

2. Adding Constraints on an existing column of a table

ALTER TABLE TableName 
ADD CONSTRAINT ConstraintName ConstraintDefinition

Demo Database

We will use this table to demonstrate and explain the concepts within this article

CREATE TABLE Employees (
    Id INT,
    Name VARCHAR(100),
    Mobile VARCHAR(15),
    Address varchar(300),
    Salary DECIMAL(10, 2)
);

Types of Constraints in SQL

1. NOT NULL Constraint

It is helpful to ensure that a column cannot have a NULL value.

CREATE TABLE TableName(
    .................................
    ColumnX datatype NOT NULL,
    ................................
);
                   OR

ALTER TABLE TableName
MODIFY ColumnName datatype NOT NULL;

Example :-

Let's consider a scenario, where we need to add not null constraints on column Name in the Employees table.

ALTER TABLE Employees
MODIFY Name varchar(150) NOT NULL;

Verification

To verify is constraints added in column or not, run desc query

desc Employees;

Field	   Type	        Null	  Key	Default    	Extra
Id	  int	        YES			
Name	varchar(150)	NO			
Mobile	varchar(15)	YES			
Address	varchar(300)	YES			
Salary	decimal(10,2)	YES

you can see null column against Name is "No" means null is not allowed in Name.

When you try to insert null value in Name column. you will get an error

INSERT INTO Employees VALUES 
(1, null,7654345654,"P.K.C. Nagar", 89000.0);

11:43:30 INSERT INTO Employees VALUES  (1, null,7654345654,"P.K.C. Nagar", 89000.0)
Error Code: 1048. Column 'Name' cannot be null 0.000 sec

2. UNIQUE Constraint

It is used to ensure all values in a column are unique, preventing duplicate entries.

CREATE TABLE TableName(
    .................................
    ColumnX datatype UNIQUE [KEY],
    ................................
);
                   OR

ALTER TABLE TableName
ADD CONSTRAINT UNIQUE_KEY_NAME UNIQUE (column(s));

Example :-

Let's consider a scenario, where we need to add unique constraints on column Mobile number in Employees table.

ALTER TABLE Employees
ADD CONSTRAINT mobile_unique UNIQUE (Mobile);

Verification : To verify is unique constraints added or not, run desc query on table

desc Employees;

Field	   Type	        Null	Key	Default	  Extra
Id	    int	        YES			
Name	varchar(150)	NO			
Mobile	varchar(15)	YES	UNI		
Address	varchar(300)	YES			
Salary	decimal(10,2)	YES

You can see on Mobile column Unique constraints is added. Learn Unique Constraints in depth

3. PRIMARY KEY Constraint

Primary Key is used to uniquely identifies each record in a table. Generally Primary Key is the combination of NOT NULL and UNIQUE constraints.

CREATE TABLE TableName(
   column1 datatype,
   column2 datatype,
   column3 datatype,
   .....................
   PRIMARY KEY(column(s))
);
             OR

ALTER TABLE TableName 
ADD CONSTRAINT PRIMARY KEY (column(s));

If you would like to dive deeper into the concept of primary key, their syntax, use cases and practical examples, check out our detailed guide here Understanding Primary Key in SQL.

4. FOREIGN KEY Constraint

Foreign Key helps maintain data consistency between related tables by enforcing referential integrity.

CREATE TABLE TableName (
    Column1 DataType,
    Column2 DataType,
    CONSTRAINT FK_Name FOREIGN KEY (Column1 ) REFERENCES ParentTableName(ParentColumnName)
);

Adding Foreign Key constraints on an existing column syntax

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName
FOREIGN KEY (ColumnName) REFERENCES ParentTableName(ParentColumnName);

Confused about how foreign keys works? Don’t worry! Our in-depth guide covers everything you need to clarify your doubts Read About Foreign Keys

5. CHECK Constraint

Check Constraints ensures that only data meeting certain conditions can be entered into the database.

Column-Level CHECK Constraint

Constraint is directly applied to a single column within the CREATE TABLE statement.

CREATE TABLE TableName(
    column1 datatype CHECK (condition),
    column2 datatype,
    ...
);

Example :-

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    Mobile VARCHAR(15),
    Address varchar(300),
    Salary DECIMAL(10, 2) CHECK (SALARY > 0)
);

Table-Level CHECK Constraint

Constraint is defined as part of the table definition and can apply to multiple columns.

CREATE TABLE TableName (
    column1 datatype,
    column2 datatype,
    ...
    CHECK (condition(s))
);

Example :-

CREATE TABLE Employees (
    Id INT PRIMARY KEY,
    Name VARCHAR(100),
    Mobile VARCHAR(15),
    Address varchar(300),
    Salary DECIMAL(10, 2),
    CHECK (SALARY > 0)
);

Adding a CHECK Constraint to an Existing Table

If you need to add CHECK constraints on an existing table. you can do this with the help of ALTER TABLE statement.

ALTER TABLE TableName
ADD CONSTRAINT ConstraintName CHECK (condition);

Example :-

ALTER TABLE Employees
ADD CONSTRAINT ck_salary CHECK (Salary > 0);

If you would like to dive deeper into the ALTER TABLE statement, their syntax, use cases and practical examples, check out our detailed article Understanding ALTER TABLE in SQL

6. DEFAULT Constraint

DEFAULT Constraints is useful to sets a default value for a column if no value is provided.

Adding DEFAULT Constraints at the time of table creation

CREATE TABLE TableName(
    ....................................
    columnX datatype DEFAULT default_value,
    ...................................
);

Adding DEFAULT Constraints on an existing column

ALTER TABLE TableName
ALTER COLUMN ColumnName SET DEFAULT default_value;

Example

Adding DEFAULT Constraints on Name column. if name is not provided set default name as "UnKnown"

CREATE TABLE Employees (
    Id INT,
    Name VARCHAR(100) default "UnKnown",
    Mobile VARCHAR(15),
    Address varchar(300),
    Salary DECIMAL(10, 2)
);

Verification : To verify what default constraints run describe query on table and check default column against with column name.

Desc Employees;

Field	  Type	        Null	Key	Default	   Extra
Id	    int	        YES			
Name	varchar(100)	YES		UnKnown	
Mobile	varchar(15)	YES			
Address	varchar(300)	YES			
Salary	decimal(10,2)	YES

Removing DEFAULT Constraint

If you need to remove the DEFAULT constraint, use the ALTER TABLE statement.

ALTER TABLE TableName
ALTER COLUMN ColumnName DROP DEFAULT;

7. INDEX Constraint

INDEX constraints is used to improve performance. it Improves the speed of data retrieval.

CREATE INDEX idx_name
ON TableName (column(s));

Dropping or Deleting a Constraint

Dropping a constraints syntax varies across different RDBMS, below is the syntax of some popular RDBMS syntax

1. In MySQL

In MySQL, you need to first identify the name of the constraint (e.g., primary key, foreign key, unique, etc.) after that based on constraint you can follow below syntax to delete constraints

1. Drop a Primary Key:

ALTER TABLE TableName
DROP PRIMARY KEY;

2. Drop a Foreign Key

ALTER TABLE TableName
DROP FOREIGN KEY ConstraintName;

3. Drop a Unique Key Constraint

ALTER TABLE TableName
DROP INDEX ConstraintName;

2. In PostgreSQL

In PostgreSQL, you can drop constraints by referencing the constraint name, below is the query syntax

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;

Same query syntax will be followed for all type of constraints like Primary Key , Foreign Key , Unique Key etc

3. In SQL Server

In SQL Server, the syntax is similar to that of PostgreSQL

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;

Same query syntax will be followed for all type of constraints like Primary Key , Foreign Key , Unique Key etc

4. In Oracle

In Oracle, the syntax for dropping constraints is also similar to SQL Server and PostgreSQL.

ALTER TABLE TableName
DROP CONSTRAINT ConstraintName;

Same query syntax will be followed for all type of constraints deletion like Primary Key , Foreign Key , Unique Key etc

Key Points

Constraints are validated when data is inserted or updated to ensure that it meets the required conditions.
Constraints apply at the column or table level.
Constraints ensure the accuracy and reliability of data.
Constraints are enforced by the database engine, reducing the need for manual validation.
Test all constraints in a development environment before deploying to production.
Constraints like FOREIGN KEY or CHECK may slightly impact performance during data insertion or updates due to validation.
Constraints like UNIQUE and PRIMARY KEY automatically create indexes, which improve query performance but may slow down insert operations.