How to create a unique index on SQL Server table with null values
A Unique Index is one where all the values in that field across all the records are unique. There are times when you want an unique index on a field in a table and that table has multiple records with a null value. It's possible to create a unique index on a field with multiple records with null values in the fields.
Run the following script in Sql Server to understand what I mean.
Create table tmpTble
(
Id int identity(1,1),
CarMake varchar(20),
Colour varchar(20)
)
insert into tmpTble values ('Peugout','Red')
insert into tmpTble values ('Ford','Green')
Insert into tmpTble values ('Chrysler','Blue')
Insert into tmpTble values ('Citreon',null)
Insert into tmpTble values ('Tesla',null)
Now create the index as you would normally.
create unique Index idx_colour on tmpTble(colour)
When you run the line, you will get the following error :-
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.tmpTble' and the index name 'idx_colour'. The duplicate key value is (
To get round the error, run the following SQL. You can see the additional where clause to exclude nulls.
create unique Index idx_colour on tmpTble(colour) where Colour is not null
The index will now be created.
Last Modified : June 2023