Constraints in SQL Server
- Constraint is used for applying conditions/restrictions for the tables in the SQL Server or any other database.
- Constraints can be applied to a table or its columns.
- If the constraint is applied by using ALTER Command, it's called Column level constraint.
- If the constraint is applied by using CREATE Command, it's called Table level constraint.
- We can apply constraints on single as well as multiple columns.
Whenever we apply constraint on a column in a table, that column restricts the user to enter only specific data.
Types of constraints in SQL Server
- Null Constraint
- Not Null Constraint
- Primary key constraint
- Unique key constraint
- Checked constraint
- Foreign key constraint
- Composite primary key constraint
- Default constraint
- Candidate key constraint
Null constraint
Null constraint is used to allow null values on a column in a table. Whenever we create a table say (Employee table) with columns EmployeeId ,EmployeeName and EmployeeDesignation, then each column accepts null value, by default.
EmployeeTable
| EmployeeId | EmployeeName | EmployeeDesignation |
| 1200 | Khaja | Software Developer |
| 1201 | Moiz | Tester |
| 1202 | Gagan | Software Trainee |
| 1203 | Nagapavan | Manager |
We can apply null constraint in two different ways -
- While creating a table
- After creating the table.
Syntax for Creating/Applying Null constraint while creating a table
- Create table tablename(ColumnName1 datatype null)
From the above Employee Table, we can write a query for Null constraint at the time of creating a table.
- Create table EmployeeTable(EmployeeId int null, EmployeeName varchar(20) null, Employee Designation varchar(20) null)
Syntax for creating/applying Null constraint after creating a table.
- Alter table tablename alter column columnName datatype null
From the above Employee Table, we can write a query for Null constraint, after creating a table.
- Alter table EmployeeTable alter column EmployeeId int null
Not null constraint restricts the user from entering the null value into a column. Whenever we apply Not-Null constraint on a column in a table, that column will not accept null value from the user.
We can apply Not-Null constraint in two different ways
- While creating a table or
- After creating the table.
Syntax for Creating/Applying this constraint at the time of creating a table
- create table tablename(columnName datatype not null)
From the above Employee Table, we can write a query for Not Null constraint at the time of creating a table, like
- Create table EmployeeTable(EmployeeId int not null, EmployeeName varchar(20) not null, Employee Designation varchar(20) null)
From the above query, we cannot insert null values to Employeeid and Employeename because of the Not Null constraint. Not Null constraint will not accept null values.
Syntax for Creating/Applying Not Null constraint after creating a table
- Alter table tablename alter column columnName datatype not null
From the above Employee Table, we can write a query for Not Null constraint after creating a table, like
- Alter table EmployeeTable alter column EmployeeId int not null
- Alter table EmployeeTable alter column EmployeeName varchar(20) not null
Primary Key Constraint
- We can apply only one primary key constraint on a column in table. Whenever we create a table by applying Primary key constraint on a column, that column will be added with Not Null constraint.
- Primary key column will not accept duplicate values.
- Primary key column will not accept null values because Not Null constraint will be added at the creation of Primary key constraint.
- We cannot apply more than one primary key constraints on a single table.
- Whenever we apply primary key constraint on a column in a table, the Not Null constraint is applied to the primary key column and due to Not null constraint, it does not accept null value.
- If we want to apply primary key constraint after creating the table, then we need to make the column as Not Null constraint because when the table is created, each column will have a null constraint by default.
We cannot apply primary key constraints on nullable columns. Primary key constraint will have a constraint name.
Constraint Name
Constraint name is the name given to the constraint like primary key constraint, foreign key constraint, unique key, composite primary key, checked constraint. For identifying the constraint, we need to use constraint name.
Whenever we need to drop the constraint or remove the constraint which is applied to the column, we will remove that by using the constraint name.
We can apply primary key constraint in two different ways.
- While creating a table
- After creating the table.
Syntax 1
Syntax for creating/applying primary key constraint at the time of creating a table
- create table table name(columnName1 datatype primary key, columnName2 datatype)
Syntax 2
Syntax for creating/applying primary key constraint at the time of creating a table
- create table tablename(columnName datatype constraint constraintName primary key, columnName2 datatype)
From the above Employee Table we can write a query for primary keyconstraint, at the time of creating a table like
- create table Employeetable(Employeeid int primary key, EmployeeName varchar(20), Employee Designation varchar(20))
- create table Employeetable(Employeeid int constraint pk_emplo_No primary key, EmployeeName varchar(20), Employee Designation varchar(20))
Syntax 1
Syntax for creating/applying primary key constraint after creating a table,
- alter table tablename add primary key(columnName)
Syntax 2
Syntax for creating/applying primary key constraint after creating a table
- alter table tablename add constraint constraintName primary key(columnName)
Syntax for removing the primary key constraintName
- alter table tableName drop constraint constraintName
From the above Employee Table, we can write a query for primary key constraint, after creating a table.
- alter table EmployeeTable add primary key(EmployeeId)
- alter table EmployeeTable add constraint pk_employ_No primary key(EmployeeId)
Unique key constraint
- Unique key constraint will not accept duplicate values.
- Unique key constraint will accept null values.
- We can apply more than one unique key constraint to columns in a table.
- unique key constraint will have constraint name.
As unique key constraint will not accept duplicate values so we cannot insert more than one null value to a unique key column/columns.
We can apply unique key constraint in two different ways
- While creating a table or
- After creating the table.
Syntax 1
syntax for creating/applying unique key constraint at the time of creating a table
- create table tablename(columnName1 datatype unique, columnName2 datatype)
Syntax 2
syntax for creating/applying unique key constraint at the time of creating a table
- create table tablename(columnName1 datatype constraint constraintname unique, columnName2 datatype)
From the above Employee Table we can write a query for unique keyconstraint, at the time of creating a table like
- create table EmployeeTable(EmployeeId int unique, EmployeeName varchar(20), EmployeeDesignation varchar(20))
- create table EmployeeTable(EmployeeId int constraint uq_Employ_Id unique, EmployeeName varchar(20), EmployeeDesignation varchar(20))
Syntax 1
syntax for creating /applying unique key constraint after creating the table
- alter table tablename add unique(columnName)
Syntax 2
syntax for creating /applying unique key constraint after creating the table
- alter table tablename add constraint constraintname unique(columnName)
From the above Employee Table we can write a query for unique keyconstraint, after creating the table like
- alter table EmployeeTable add unique(EmployeeId)
- alter table EmployeeTable add constraint uq_emplo_id unique(EmployeeId)
Checked constraint
After creating the table user needs to enter the data to its employee table, before entering the data if we want to perform certain validations to check whether the entered data matches with the datatype of given column or not, in this scenario we need to go for checked constraint.
checked constraint accepts both null values as well as duplicate values.
We can apply checked constraint in two different ways,
- While creating a table or
- After creating the table.
Syntax 1
syntax for creating/applying checked constraint at the time of creating a table
- create table tablename(columnName1 datatype, columnName2 datatype, check(condition))
Syntax 2
syntax for creating/applying checked constraint at the time of creating a table
- create table tablename(columnName1 datatype, columnName2 datatype constraint constraintName check(condition))
From the above Employee Table we can write a query for unique keyconstraint, at the time of creating the table like
- create table EmployeeTable(EmployeeId int, EmployeeName varchar(20), EmployeeDesignation varchar(20), check(EmployeeId > 1220))
- create table EmployeeTable(EmployeeId int, EmployeeName varchar(20), EmployeeDesignation varchar(20) constraint ch_employ_id check(EmployeeId > 1220))
Syntax 1
syntax for creating/applying checked constraint after creating the table
- alter table tablename add constaint constraintName check(condition)
From the above Employee Table we can write a query for checked constraint, after creating the table like
- alter table EmployeeTable add constraint ck_employ_id check(EmployeeId > = 1220)
Foreign key constraint
Foreign key constraint is used to maintain/establish the connection/relationship between the two or more tables.
- We can have multiple foreign key constraints on a single table.
- Foreign key constraint will accept duplicate values.
- Foreign key constraint will accept null values.
- Foreign key constraint will have a constraint name.
After maintaining/establishing the connection/relationship between the two or more tables, if we want to enter the data/value in the foreign key column then the same data should exist in primary key column.
OR
We cannot insert the value in the foreign key column until and unless the data should be available in primary key column.
If we want to remove/drop primary key constraint then first we need to remove/drop the foreign key constraint.
As we maintain the connection/relationship between the two or more tables, the data available in primary key column and foreign key column should be same.
We can apply foreign key constraint in two different ways
- While creating a table or
- After creating the table.
Syntax 1
syntax for creating/applying foreign key constraint at the time of creating a table
- create table tablename(columnName1 datatype foreign key references primary key tableName(primarykey columnName))
Syntax 2
syntax for creating/applying foreign key constraint at the time of creating a table
- create table tablename(columnName1 datatype constraint constraintName foreign key references primarykey tableName(primarykey columnName))
For this example we will consider two tables like EmployeeTable with columns.
(EmployeeId, EmployeeName, EmployeeDesignation, EmployeeSalary) and SalaryIncrementTable with columns(EmployeeId, IncrementSalaryPercentage) by creating primary key for EmployeeId column in EmployeeTable and foreign key for EmployeeId in SalaryIncrementTable.
Before creating foreign key constraint column we should have primary key constraint created on a column in a table.
- create EmployeeTable with EmployeeId as primary key
- create table EmployeeTable(EmployeeId int primary key, EmployeeName varchar(20), EmployeeSalary money, EmployeeDesignation varchar(20))
From the above Employee Table & SalaryIncrementTable we can write a query for foreign keyconstraint, at the time of creating the table like,
- create table SalaryIncrementTable(EmployeeId int foreign key references EmployeeTable(EmployeeId), IncrementSalaryPercentage varchar(10))
- create table SalaryIncrementTable(EmployeeId int constraint fk_employ_id foreign key references EmployeeTable(EmployeeId), IncrementSalaryPercentage varchar(10))
Syntax 1
syntax for creating/applying foreign key constraint after creating a table
- alter table tablename add foreign key(FKcolumnName) references PKeyTableName(PKcolumnName)
Syntax 2
syntax for creating/applying foreign key constraint after creating a table
- alter table tablename add constraint constraintName foreign key(FKcolumnName) references PKeyTableName(PKcolumnName)
From the above Employee Table & SalaryIncrementTable we can write a query for foreign keyconstraint, after creating the table like
- alter table SalaryIncrementTable add foreign key(EmployeeId) references EmployeeTable(EmployeeId)
- alter table SalaryIncrementTable add constraint fk_employe_id foreign key(EmployeeId) references EmployeeTable(EmployeeId)
Composite Primary key constraint
- From the constraint name we can identify that it is similar to primary key constraint.
- Composite primary key constraint is used to apply primary key constraint to the combination of columns in a table.
- Composite primary key constraint will not accept duplicate values.
- Composite primary key constraint will not accept null values.
- We can apply only one composite primary key constraint on a single table.
- Composite primary key constraint will have a constraint name.
If we apply primary key constraint on a column in a table then not null constraint is applied on a primary key constraint column.
Similarly if we apply composite primary key constraint for the combination of columns then not null is applied on individual columns.
Syntax 1
syntax for creating/applying composite primary key constraint at the time of creating a table
- create table tablename(columnName1 datatype, columnName2 datatype, columnName3 datatype primary key(columnName1, columnName2))
Syntax 2
syntax for creating/applying composite primary key constraint at the time of creating a table
- create table tablename(columnName1 datatype, columnName2 datatype, columnName3 datatype constraint constraintName primary key(columnName1, columnName2))
From the above Employee Table we can write a query for Composite Primary keyconstraint, at the time of creating the table like
- create table Employee Table(EmployeeId int, EmployeeName varchar(20), EmployeeSalary money, EmployeeDesignation varchar(20) primary key(EmployeeId, EmployeeName))
- create table Employee Table(EmployeeId int, EmployeeName varchar(20), EmployeeSalary money, EmployeeDesignation varchar(20) constraint ck_empl_columns primary key(EmployeeId, EmployeeName))
Syntax 1
syntax for creating/applying composite primary key constraint after creating the table
- alter table tablename add primary key(columnName1, columnName2)
Syntax 2
syntax for creating/applying composite primary key constraint after creating the table
- alter table tablename add constraint constraintName primary key(columnName1, columnName2)
From the above Employee Table we can write a query for Composite Primary keyconstraint, after creating the table like
- alter table EmployeeTable add primary key(EmployeeId, EmployeeName)
- alter table EmployeeTable add constraint ck_emplo_columns primary key(EmployeeId, EmployeeName)
Default constraint
We can use default constraint for providing default values to a column instead of null value. After creating a table, if we want to add a new column to the existing table instead of null values in the column we will provide with default values.
syntax for creating default constraint at the time of creating a table
- create table tablename(columnName1 datatype, columnName2 datatype, columnName3 datatype
- default (defaultvalue))
From the above Employee Table we can write a query for default constraint, at the time of creating the table like
- create table EmployeeTable(EmployeeId int
- default (10), EmployeeName varchar(20), EmployeeSalary money, EmployeeDesignation varchar(20))
Candidate key constraint
- Candidate key constraint is a combination of unique key and not null constraint.
- Candidate key constraint will not accept duplicate values because its combination of unique key and not null constraint.
- Candidate key constraint will not accept null values because its combination of unique key and not null constraint.
- We can apply/create more than one candidate key constraint on a single table.
- Candidate key will have a constraint name.
- we know that we can apply only one primary key constraint on a single table, if we have a scenarios like EmployeeId ,pancard number we can't apply primary key to both the columns in this situation; we will apply candidate key constraint to pancard column to avoid duplication of data.
Syntax for applying/creating candidate key constraint at the time of creating a table
- create table tablename(columnName1 datatype, columnName2 datatype unique not null)
From the above Employee Table we can write a query for candidate key constraint, at the time of creating the table like this:
- create table EmployeeTable(EmployeeId int unique not null, EmployeeName varchar(20), EmployeeSalary money, EmployeeDesignation varchar(20))
Thanks and I hope this article helps you.