Sql server Constraints – Primary Key, Foreign Key, Unique Key, Not Null, Check Constraints

A constraint is a property that is assigned to a column or a group of columns to prevent incorrect or corrupt data from entering into the tables. These constraints ensure the accuracy and reliability of the data into the tables. This article will tell about the Sql server Constraints – Primary Key, Foreign Key, Unique Key, Not Null, Check ConstraintsLet’s suppose we have two tables Employee and Department whose description is given below:-

CREATE TABLE [dbo].[Employee](
[Empid] [int] IDENTITY(1,1) NOT NULL Primary key ,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLastName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Managerid] [int] NULL,
[Departmentid] [INT]
)

CREATE TABLE [dbo].[Department](
[Departmenttid] [int] IDENTITY(1,1) NOT NULL primary key ,
[DepartmentName] [nvarchar](255) NOT NULL
)

There are following types of constraints in the SQL Server:-

• Primary Key
• Foreign Key
• Unique Key
• Not Null
• Check Constraints

Primary Key

Primary key is used to uniquely identify a row in a table. A table can have only one primary key. Primary keys don’t allow null values. The data help across the primary key column must be unique. It can be defined at the column level or at the table level.
Primary Key can be defined while creating a table with Create Table command or it can be added with the Alter table command.

Syntax for Primary Key

(ColumnName) (DataType) ((Size)) Primary Key

Primary key defined at the column level

Example

Suppose we want to create a table DepartmentManager which contains the information of the manager for the departments.

create table DepartmentManager(Departmentmanagerid int identity(1,1) primary key,empid int, Departmenttid int)

This table contains the Empid (id of the employee from the table Employee), Departmenttid (from the Department table)and Departmentmanagerid which is the identity column and primary column too.

Primary key defined at the table level

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int, Departmenttid int, primary key(Departmentmanagerid))

Primary key (known as composite primary key) can be a combination of two or more than two columns. If a primary key is a combination of two or more than two columns then it can only be defined at the table level only. For Example if we want that the primary key should be the combination of two columns empid and Departmenttid of the table DepartmentManager,then the required query will be

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int, Departmenttid int, primary key(empid, Departmenttid))

Adding Primary Key constraint using Alter table command

The query for Adding a primary key with the Alter Table command is as follows:-

Syntax

Alter Table tablename Add constraint constrainname Primary Key (Columnname)

Suppose there is no primary key defined for the table employeemaster and we want to add a primary key constraints on the column empid of the table employeemaster with alter table command then the required query should be:-

Drop table DepartmentManager

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int, Departmenttid int)

Alter Table employeemaster add constraint pk_EmpPrimaryKey primary key(empid)

Dropping a primary constraint from a table

The command for dropping a primary key is given below:-

Alter Table tablename Drop constraint name

For Example if we want to drop the constraint pk_EmpPrimaryKey from the table employeemaster,the required query will be

alter table DepartmentManager drop constraint pk_EmpPrimaryKey

Foreign Key

Foreign key is used to prevent destroying the link between two tables. In foreign key, the table (Child table) in which the foreign key is defined points to the primary column of another table (Master table). A foreign key can points to the primary column of the same table. In this Foreign key relationship is said to be defined within the same table. Due to foreign key relationship, a value from the primary column of the master table can not be deleted until its all references from the child tables are deleted. Also a new value in the column in which primary key is defined can not be inserted until the value is already existed in the primary column of the master table.

Syntax for defining the Foreign key is:-

(Column Name)(data type) ((Size)) References (Table Name) [((Column Name))]

For example:-Suppose we want to create a table DepartmentManager whose column empid references to the empid column of the Employee table.Then the query will be

create table DepartmentManager(Departmentmanagerid int identity(1,1) primary key, empid int references Employee(empid), Departmenttid int )

Foreign Key Constraints can also be added with the alter table command. For example, if we want that the departmentid column of the table DepartmentManager references to the Departmentid column of the table Department,then the query will be:-

Alter Table DepartmentManager Add Constraint Fk_Departmenttid Foreign Key(Departmenttid)references Department (Departmenttid)

The above defined foreign key constraint can also be dropped from the table by using the following query:-

Alter Table DepartmentManager Drop Constraint Fk_Departmenttid

The foreign key columns of a table can also references to columns of the same table.
For Example, if Managerid is the foreign key column of the table Employee which references to the empid column of the same table then the required query will be:-

CREATE TABLE [dbo].[Employee](
[Empid] [int] IDENTITY(1,1) NOT NULL Primary key ,
[EmpNumber] [nvarchar](50) NOT NULL,
[EmpFirstName] [nvarchar](150) NOT NULL,
[EmpLastName] [nvarchar](150) NULL,
[EmpEmail] [nvarchar](150) NULL,
[Managerid] [int] references employee (empid) NULL,
[Departmentid] [INT]
)

Unique Key

Unique key constraint enforces that the column can contains only unique values on which unique constraints is defined. This column can contain multiple null values but all not null values must be unique. Unique Constraint can be defined either at the column level or at the table level.

Syntax for defining the unique constraint at the column level is:

(ColumnName) (Datatype) ((Size)) UNIQUE

Syntax for defining the unique constraint at the column level is:

Create table tablename ((col1)(datatype) ((size)), (Col2)(Datatype) ((Size)), Unique ((col1), (Col2)))

For example, if want to make the empid column of the table DepartmentManager as unique,then the required query is:

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int Unique, Departmenttid int)

Also if we want to make both the columns empid and Departmenttid Unique then the required query is

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int, Departmenttid int, Unique(empid, Departmenttid))

Like primary key and the foreign key, Unique Key can be added through Alter Table Command. The syntax is:-

Alter table tablename add constraint constraintname Unique (Columnname)

Suppose we have created a table DepartmentManager without any unique Key and
We want to add a unique key constraint to the table with alter table command then the required query is:-

Drop table DepartmentManager

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int, Departmenttid int)

alter table employeesalarymaster add constraint Uni_empid unique (empid)

Dropping a Unique constraint from a table
The command for dropping a Unique key is given below:-

Alter Table tablename Drop constraint name

For Example if we want to drop the constraint Uni_empid from the table employeemaster,the required query will be

alter table DepartmentManager drop constraint Uni_empid

Not Null

Not Null Constraint enforces that the column in which not null constraint is defined can not contains null values.

Not Null constraints can only be defined at the column level only. It ensures that the column of the table on which it is defined can not be left blank.

The syntax for defining the Not Null Constraint is:-

(Column Name)(Data Type ((size)))NOT Null

For example, if want to make the empid column of the table DepartmentManager is Not Null,then the required query is:

create table DepartmentManager(Departmentmanagerid int identity(1,1),empid int Not Null, Departmenttid int)

Check Constraints

Check constraints are the user defined business rules which can be applied to the database table column. For example a check constraint on the column “Salary” of the table Employee salary can be defined which state that no employee can have salary less than 5000.

Check Constraint can be defined as the column level or the table level.

The Syntax for defining the check constraints at the column level is

(Column Name)(Data type) ((Size)) Check ((Logical Expression))

For Example: – Suppose we want to create a table Employeesalarymaster which have a column name Empsalary which contains the salary of an employee.Now If we want that only those rows inserted into the table whose Empsalary Column value is greater than 5000 then we can use the check constraint in the following way.

CREATE TABLE Employeesalarymaster(empsalaryid int identity(1,1),Empid int, Empsalary numeric(10,2) check (empsalary)5000) not null, SalaryDt datetime)

The Syntax for defining the check constraints at the column level is

Check ((Logical Expression))

For Example: – The same table Employeesalarymaster can be created by defining the check constraint at the table level whose syntax is given below:-

CREATE TABLE employeesalarymaster(empsalaryid int identity(1,1),Empid int, Empsalary numeric(10,2) not null,SalaryDt datetime,check (empsalary)5000))

Note: – The Check constraints must be a Boolean expression that can be evaluated using the values in the rows being inserted or updated.

CodeProject