SQL Server Constraints
Constraints in SQL Server are rules and restrictions applied on a column or table so unwanted data can't be inserted into tables. Constraints maintain the data integrity and accuracy in the table. This ensures the accuracy and reliability of the data in the database. We can create constraints on single or multiple columns of any table.
Constraints can be classified into the following two types.
Column Types Constraints
Definitions of these types of constraints are given when the table is created.
Create Table My_Constraint
(
IID int NOT NULL,
Salary int CHECK(Salary>5000)
)
Table Types Constraints
Definitions of these types of constraints are given after the creation of the table using the Alter Command.
- Alter Table My_Cosntraint
- Add constraint Check_Constraint Check(Age>50)
SQL Server contains the following 6 types of constraints
- Not Null Constraint
- Check Constraint
- Default Constraint
- Unique Constraint
- Primary Constraint
- Foreign Constraint
Let us understand each constraint briefly.
Not Null Constraint
A Not null constraint restricts the insertion of null values into a column. If we are using a Not Null Constraint for a column, then we cannot ignore the value of this column during an insert of data into the table.
Column Level
Syntax
CREATE TABLE Table_Name
(
Column_Name Datatype CONSTRAINT Constraint_Name NOT NULL,
);
Example
Create Table My_Constraint
(
IID int NOT NULL,
Name nvarchar(50) CONSTRAINT Cons_NotNull not null,
Age int Not Null,
)
Table Level
Syntax
ALTER TABLE Table_Name
ALTER COLUMN Column_Name Datatype NOT NULL
Example
Alter Table My_Constraint
Alter Column IId int Not Null
Without SQL Command
We can also create a Not Null constraint in Microsoft SQL Server without the execution of a SQL query.
First, right-click on the table and select and click on the design option. Now check all the columns in the "Allow Nulls" option that should have a Null Value.
![]()
Figure 1 - Table
Check Constraint
A Check constraint checks for a specific condition before inserting data into a table. If the data passes all the Check constraints, then the data will be inserted into the table; otherwise, the data for insertion will be discarded. The CHECK constraint ensures that all values in a column satisfy certain conditions.
Column Level
Syntax
Create Table Table_Name
(
Column_Name Datatype Constraint Constraint_Name Check(Condition)
)
Example
Create Table Constraint_
(
IId int Constraint Constraint_Name Check(IId>100)
)
Table Level
Syntax
Alter Table Table_Name
Add Constraint Constraint_Name Check(Condition)
Example
Alter table Constraint_
Add constraint Cons_Name Check(IId>150)
Without SQL Command
First, go to Table Design, right-click on the Column_Name containing a check constraint, and select the "Check Constraint" option. Then, a new window will be shown. In this window, add a constraint and define it in the Expression Field.
![]()
Figure 2 - Check Constraint
![]()
Figure 3 - Select Check Constraint
Default Constraint
Specifies a default value when a value is not specified for this column. If in an insertion query, any value is not specified for this column, then the default value will be inserted into the column.
Column Level
Syntax
Create Table Table_Name
(
Column_Name DataType Constraint Constraint_Name Default(Value),
)
Example
Create Table My_Table1
(
IId int default(1500),
Name Nvarchar(50)Constraint Name_Default Default('Pankaj'),
Age Int,
Salary Int Default(100)
)
Table Level
Syntax
Alter Table Tabel_Name
Add Constraint Constraint_Name Default(Value) for[Column_Name]
Example
Alter Table My_Table1
Add Constraint cons_Default Default(40) for[Age]
Without SQL Command
Go to Table Design, click on the specific column name that should have a default value, and go to the column Property and provide the default value.
![]()
Figure 4 - Column Property
Unique Constraint
It ensures that each row for a column must have a unique value. It is like a Primary key but can accept only one null value. In a table, one or more columns can contain a Unique Constraint.
Column Level
Syntax
Create Table Table_Name
(
Column_Name Datatype Constraint Constraint_Name Unique
)
Example
Create Table MY_Tab
(
IId int constraint Unique_Cons Unique ,
Name nvarchar(50)
)
Table Level
Syntax
Alter Table_Name
Add Constraint Constraint_Name Unique(Column_Name)
Example
Alter Table My_Tab
Add Constraint Unique_Cons_ Unique(Name)
Without SQL Command
First, go to the Table definition, select a column, and right-click on that column. Now select the option Index/Keys. Add a constraint and mark its "Is Unique" option as True. Now a window will be shown.
![]()
Figure 5 - Indexes & Keys
![]()
Figure 6 - Select Indexes
Primary Key Constraint
A Primary key uniquely identifies each row in a table. One or more of the columns of a table can contain a Primary key. It cannot accept null and duplicate data.
Column Level
Syntax
Create Table Table_Name
(
Column_Name Datatype Constraint Constraint_Name Primary Key,
)
Example
Create Table Employee
(
IId int constraint Const_primary_IId primary key,
Name nvarchar(50)
)
Table Level
Syntax
Alter Table Table_Name
Add constraint Constraint_Name Primary Key(Column_Name)
Example
Alter Table Employee
Add constraint Constraint_Name Primary Key(IId)
Without SqlQuery
First, go to the table design, right-click Column, and select the "Set Primary Key" Option.
![]()
Figure 7 - Set Primary Key
Foreign Key Constraint
A Foreign Key is a field in a database table that is a Primary key in another table. A Foreign key creates a relation between two tables. The first table contains a primary key, and the second one contains a foreign one.
Column Level
Syntax
Create Table Table_Name
(
Column_Name Datatype Constraint Constraint_Name References Reference_Table_Name(Reference_Column_Name)
)
Example
Create Table Employee_
(
IId int constraint Cons_Reference References My_Constraint(IId),
Age int,
Salary int
)
Table Level
Syntax
ALTER TABLE Table_Name
ADD CONSTRAINT Constraint_Name FOREIGN KEY(Column_Name)
REFERENCES Reference_Table (Column_Name)
Example
ALTER TABLE Employee_
ADD CONSTRAINT Cons_Emp_Foreign FOREIGN KEY(IId)
REFERENCES My_Constraint(IId)
Without SQL Command
First, go to the table design, right-click on the column, and select the "Relationship" option. Now a window will be shown. In this window, click on the "Table and Column Specificat" option and select Primary Key table, Column name, and Column name for the foreign key.
![]()
Figure 8 - Column Relationships
![]()
Figure 9 - Foreign Key Relationships
Summary
In this article, we learned about constraints in SQL Server and how to create using SQL.