Unique key

1) Uniquely identify every record in the table.

2) It allows only one Null Value.

3) When we create unique key then unique Non-Clustered index is create.

4) Unique key Can be made foreign key on another table

1. while creating Table

Create Table Emp

(

EmpId Int Identity(1,1) Unique,

Name Varchar(50) Not Null,

Age Int check (Age>=20),

CreatedDate DateTime Default getdate()

)

2. while creating Table using constraint

Create Table Emp

(

EmpId Int Identity(1,1) ,

Name Varchar(50) Not Null,

Age Int check (Age>=20),

CreatedDate DateTime Default getdate()

Constraint Const_UK Unique(EmpId)

)

3. After Table creation 

On New Column 

Alter Table Emp

Add AccNo varchar(20) Unique

On Existing Column

Alter Table Emp

Add Constraint Const_Unique Unique(Name)

Drop Constraint

Alter Table Emp

Drop Constraint Const_Unique

 

Scenario

Create Table Emp

(

EmpId Int Unique,

Name Varchar(50) Not Null,

Age Int check (Age>=20)

)

Insert Into Emp Values (1,'Sagar',28)

Insert Into Emp Values (1,'Nitin',27)---Error

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint 'UQ__Emp__AF2DBB9895978C01'. Cannot insert duplicate key in object 'dbo.Emp'. The duplicate key value is (1).

The statement has been terminated.

Insert Into Emp Values (2,'Ketan',27)

Select * from Emp

Update Emp Set EmpId=5 Where EmpId=2----Possible

Update Emp Set EmpId=1 Where EmpId=5----Error because Empid=1 already present

Update Emp Set EmpId=NULL Where EmpId=5----Possible(one Null allow)

Composite Uniqe key - multiple columns in unique clause

Alter Table Emp

Add Constraint const_Comp_UK Unique(EmpId,Name)

OR

Create Table Emp

(

EmpId Int Unique,

Name Varchar(50),

Age Int check (Age>=20),

CreatedDate DateTime Default getdate()

Unique(EmpId,Name)

)

Insert Into Emp(EmpId,Name,Age) Values(1,'sagar',28)---allow

Insert Into Emp(EmpId,Name,Age) Values(1,'Kishor',28)---Error

--Violation of UNIQUE KEY constraint 'UQ__Emp__AF2DBB98F5EE3A1B'. Cannot insert duplicate key in object 'dbo.Emp'

Insert Into Emp(EmpId,Name,Age) Values(null,null,28)---allow

Insert Into Emp(EmpId,Name,Age) Values(null,null,28)---Error

--Violation of UNIQUE KEY constraint 'UQ__Emp__C81AE3D7471897CC'. Cannot insert duplicate key in object 'dbo.Emp'. The duplicate key value is (<NULL>, <NULL>)

Difference between Unique Key & Unique Constraint:

1)  Both Does Not Allow Duplicate Value.

2)  Error

Violation of a unique constraint returns error 2627.

Violation of a unique index returns error 2601.

Unique index ensures that the values in the index key columns are unique.

Unique constraint also guarantees that no duplicate values can be inserted into the column(s) on which the constraint is created. When a unique constraint is created a corresponding unique index is automatically created on the column(s).