A Unique Key is a type of constraint in SQL Server that ensures the values in a column (or a group of columns) are distinct across all rows in the table. Unlike a Primary Key, which also enforces uniqueness, a table can have multiple Unique Keys—but only one Primary Key.
Unique Keys are especially useful when you want to prevent duplicate values in fields like Email, Username, or PhoneNumber, even if they’re not part of the table’s primary identifier
Steps to Create a Unique Key Using SSMS:
- Open SSMS
- Go to the Database
- Select the table on which you want to create Unique Constraint
- Right click on the Table e.g. Employee then Click on Design
- Right Click on the column on which you want to create Unique Constraint
- Select Indexes /Keys
- Set Type as Unique Key
- Click Close
- Save the table changes
How to test whether Unique Key is working or not
Currently we have 2 records in the Employee table with EMPID 101 & 102. If we insert another new record of employee with EmpID 101 or EmpID 102 then SQL Server should give error.
When we tried to insert new employee data with EmpId – 101 then it is giving the error:
Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint ‘IX_Employee’.
Cannot insert duplicate key in object ‘dbo.Employee’. The duplicate key value is (101).
The statement has been terminated.
Creating Unique Constraint using T-SQL:
ALTER TABLE dbo.Employee
ADD CONSTRAINT IX_Employee UNIQUE (EmpID);