SQL Server - Unique Key Constraint

What is unique key constraint in SQL Server ?

The name it self indicate that unique key constraint means, it will not allow to insert duplicate value in particular columns. All values in that columns must be unique.

Note : It is similar to primary key but Unique Key allow one NULL value to store in columns where primary key not allow to store null value in columns.

1) Add Unique constraint on table by Query.
In below example we are creating new table of tblStudentDetails and we will apply unique key to RollNumber columns.

Note : In above example we set NOT NULL and UNIQUE key constraint on one column that is RollNumber. Because we not insert null value in student roll number.

2) Add UNIQUE key constraint on table using table design.

Step 1 :

Step 2 :

Step 3 : Save table.

Table already having five rows and RollNumber columns has unique values. Consider below result set of tblStudentDetails table.

Let's try to insert duplicate value in RollNumber columns.

Example

Copy

INSERT INTO tblStudentDetails (RollNumber, StudentName, City)
VALUES (3, 'Jyo', 'Banglore')

Output

Note : In above example table already have the roll number 3 student so Unique key not allowing to store duplicate value in it and it give the error 'Violation of UNIQUE KEY constraint.'

To remove the error insert correct data in table.

Example

Copy

INSERT INTO tblStudentDetails (RollNumber, StudentName, City)
VALUES (6, 'Jyo', 'Banglore')

Output

List student table details to see inserted data.