Insert Multiple Null value in an Unique Field through NONCLUSTERED Index

Read Previous Post: Allowing only two null values for a column in SQL table

Problem Statement: We want a unique constraint in such a way that, it maintains uniqueness of the values in the field along with it; it allows entering multiple NULL value in the field.

There is an exception with unique constraint, which evaluate two null values as duplicate whereas, ANSI says any operation with NULL evaluate to UNKNOWN.  Here we are trying to find the alternative way to insert multiple null value in Unique filed through NONCLUTERED Index.

We can implement the same using trigger, as we demonstrate in previous post; here we will try to implement the same using UNIQUE NONCLUSTERED Index.

Here we are creating table with three column id, name and salary.

create table TestMutipleNull_TB

(id int identity(1,1) primary key,

name varchar(10),

salary decimal)

Command(s) completed successfully.

Table creation is done; let’s create a UNIQUE NON CLUSTERTED Index on name field.

create unique nonclustered index ix_TestMultipleNull_TB_name

on TestMutipleNull_TB(name) where name is not null

Command(s) completed successfully.

Here the index has where condition which says name is not null, so the uniqueness will applied to only non-null value keeping null value aside, which is our objective to do in this article.

Lets insert some data in the table.

insert into TestMutipleNull_TB

select 'Babu',null union all

select 'Rinny',30000 union all

select 'Binny',null union all

select 'Lopa', 50000

Now here we are trying to insert a name which is present in the table.

insert into TestMutipleNull_TB values('Babu',40000)

Msg 2601, Level 14, State 1, Line 17

Cannot insert duplicate key row in object 'dbo.TestMutipleNull_TB' with unique index 'ix_TestMultipleNull_TB_salary'. The duplicate key value is (Babu).

The statement has been terminated.

Since the same name is present in the table, the insertion fails. This means uniqueness is maintained.

Now lets try to insert multiple null values in the name field.

insert into TestMutipleNull_TB values(null,40000)

(1 row(s) affected)

insert into TestMutipleNull_TB values(null,200)

(1 row(s) affected)

select * from TestMutipleNull_TB(nolock)

id          name       salary

----------- ---------- ---------

1           Babu       NULL

2           Rinny      30000

3           Binny      NULL

4           Lopa       50000

6           NULL       40000

7           NULL       200

So here we saw multiple null values gets inserted into name field but it maintain the uniqueness of non null values.

Conclusion: You can use NONCLUSTERED Index to allow multiple null values keeping other values unique.

Software Engineer: Life became NULL if you go away my darling; me and my code :)

Post Reference: Vikram Aristocratic Elfin Share