Insert Null Value in Unique column (Enforcing Uniqueness with NULL)

In a table ( Emp)  we have a unique column  PAN number. PAN Number is unique for every employee. Our requirement is that we need to insert null value those don’t have PAN number. All we know that in the unique column it allows only one null value.

Read: Difference between PRIMARYKEY and UNIQUE KEY

http://bageshkumarbagi-msbi.blogspot.com/2017/05/difference-between-primary-key-and.html

See the demo

Creating the emp table with unique key index

CREATE TABLE emp(                 ID int IDENTITY(1,1) NOT NULL,                 E_name varchar(50) NULL,                 E_mobile varchar(10) NULL,                 E_add  varchar(50) NULL,                 PAN_Number     varchar(10)                 ) CREATE UNIQUE INDEX UX_emp_PAN_Number ON emp (PAN_Number) ;

Table and index created successfully.

 

Now I am inserting some records in this table

Insert into emp (E_name,E_mobile,E_add,PAN_Number)values ('Bagesh','888880XXXX','Greater Noida','PAN123BAGI') Insert into emp (E_name,E_mobile,E_add,PAN_Number)values ('Rajesh','888880AAAA','Noida','PAN123RAJE') Insert into emp (E_name,E_mobile,E_add,PAN_Number)values ('Kumar','888880KKKK','Delhi','PAN123KUMA') Insert into emp (E_name,E_mobile,E_add,PAN_Number)values ('Ravi','888880RRRR','Pune','PAN123RAVI') Insert into emp (E_name,E_mobile,E_add)values ('Rakesh','888880RRRR','Pune')

See records in the table

  

Now I am inserting one more record which PAN number is null.

Insert into emp (E_name,E_mobile,E_add)values ('Mohan','888880MMMM','Pune')

Running this query

 

Oooo it does not allow us to insert another record.

But our business requirement is to allow these records in the table. To overcome this issue, when we are creating the unique key index we need to allows filtered indexes where the index is created only for a subset of the data in the table.

Now I am dropping the existing index.

 

Now I am creating the filtered indexes

CREATE UNIQUE INDEX UX_emp_PAN_Number ON emp (PAN_Number) WHERE PAN_Number IS NOT NULL

 

Unique key index is created successfully. Now I am inserting above record.

 

Record inserted successfully with NULL value. See the records in the table.

Keep In Mind only it allow null value. If we try to insert some other duplicate Pan number it will not allow us to insert. See below

Hope this will help on such type of scenarios.

Thanks!  Keep reading!!!!!!!!!!!!!