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.

Example

CREATE TABLE tblStudentDetails
(
Id INT ,
RollNumber INT NOT NULL UNIQUE ,
StudentName VARCHAR (50) NOT NULL,
City VARCHAR (50)
)

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 :


create unique key constraint in sql server

Step 2 :


create unique key constraint in sql server

Step 3 : Save table.


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

unique key example in sql server

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

Example

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

Output

Violation of UNIQUE KEY constraint


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

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

Output

Violation of UNIQUE KEY constraint solution


List student table details to see inserted data.

Violation of UNIQUE KEY constraint solution output