Articles → SQL SERVER → Unique Index In SQL Server

Unique Index In SQL Server

In this article, we will discuss the unique index in the SQL server.

Purpose Of Unique Index

If the unique index is created on a column, then it means: -

Only unique values are allowed
NULL value could be inserted

Syntax

CREATE UNIQUE INDEX index_name ON table_name (column_name)

Example

First, create a table. See the screenshot for the table schema.

Picture showing the schema of the sample table

Click to Enlarge

Now, add the unique index on the column "Test" using the following syntax.

CREATE UNIQUE INDEX idx_unique_SampleTable_Test ON SampleTable (Test)

Next thing is to test if a unique index is created successfully or not. For that, we will execute the following insert queries.

insert into SampleTable 
values 
  (null) insert into SampleTable 
values 
  ('value1') insert into SampleTable 
values 
  ('value2') insert into SampleTable 
values 
  ('value1')

When you execute this query, you will get the following output.

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)
Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object 'dbo.SampleTable' with unique index 'idx_unique_SampleTable_Test'. The duplicate key value is (value1).
The statement has been terminated.

The reason for this error is, we are trying to insert a duplicate value in the column. But a unique index is created on that column. That is why an error is thrown.

Posted By -	Karan Gupta

Posted On -	Sunday, January 1, 2017

Query/Feedback

Your Email Id

Subject

Query/Feedback	Characters remaining 250