Unique Constraint in SQL Server

 Unique constraint enforce uniqueness of the values in a column. It ensures all the values in a column are different. This can be created on Nullable columns and would thus treat NULL as one of the possible value.

Like primary keys, unique constraints belong to the logical database design and are implemented as unique, nonclustered indexes on the physical level.

We can have many unique constraints per table.

It is a good idea to have uniqueness enforced when data is unique. This helps to keep the data clean and avoids data integrity issues. Unique constraints can also help Query Optimizer to generate more efficient execution plans.

Example:

create table dbo.Employee_Details

(

EmployeeId int not null,

Name nvarchar(64) not null,

SSN char(9) not null constraint UQ_Employees_SSN unique,

DepartmentCode varchar(32) not null,

IntraDepartmentCode varchar(32) not null,

constraint UQ_Employees_Codes unique(DepartmentCode, IntraDepartmentCode)

)

Alter and Drop Constraint

Alter table Employee_Details add constraint Employee_Details_UK unique(EmployeeId)

Alter table Employee_Details drop constraint Employee_Details_UK

Different types of SQL Server Constraints:

• Not Null Constraint
• Check Constraint
• Default Constraint
• Unique Constraint
• Primary Key Constraint
• Foreign Key Constraint