Multiple NULL values in a Unique index in SQL Server/DB2 LUW

Yesterday, when helping out a friend who was working on a project that required porting an application from Oracle v9.2.0.5 to SQL Server 2005, he ran into the same UNIQUE index issue as we had blogged before. Since that was a major requirement by the client, this project needed to support having multiple NULL values in the column and still have a UNIQUE constraint. That is allowed by Oracle but not in SQL Server and DB2 LUW.

There are two ways to make this work in SQL Server and DB2 LUW also –> one requires a work-around while the other one requires a trigger.

Consider this table:

CREATE TABLE TEST_UQ (COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 NVARCHAR(10) NULL)
GO

In this table, COL1 has been declared as the primary key but we want a UNIQUE constraint to be put on COL2 as well. Please note that COL2 is a nullable column and that SQL Server does not allow multiple NULL values in a UNIQUE index and treats them the same way. We can test it out prior to proceeding with the work-around:

Let’s create a unique index first:

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL2)
GO

Now, let us try to insert these values:

insert into test_uq (col2) values (‘abc’);
insert into test_uq (col2) values (‘xyz’);
insert into test_uq (col2) values (Null);

All three will go in. After that, try to insert the NULL value again:

insert into test_uq (col2) values (Null);

and you will get the error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index ‘TEST_UQ_IND_1’.
The statement has been terminated.

Solution 1:

The work-around is to have a computed column and define the unique constraint on it. Here is how you can do that:

1) First, let’s drop the existing unique index:

drop index test_uq.TEST_UQ_IND_1

2) Next, let’s add the computed column:

ALTER TABLE TEST_UQ ADD COL3 AS (CASE WHEN COL2 IS NULL THEN CAST(COL1 AS NVARCHAR(10)) ELSE COL2 END);

In this command, we are stating that whenever the value for COL2 is null, replace it with the primary key after casting it to the same data-type as that of COL2. By doing so, we will mae sure that COL3 is always NOT NULL and always have unique values. This approach will work well in this case as there should never be a clash of the values between COL1 and COL2. For example, what-if you needed to do this on a column that was also an interger data-type column? In that case, chances of clashes of the data can arise. If you suspect a clash, you can have additional logic like: (CASE WHEN COL2 IS NULL then -1 * COL1 ELSE COL2 END). That way, you can still maintain the logic and the uniqueness.

3) Now, create the unique index on this column:

CREATE UNIQUE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL3)
GO

4) Next, let’s try to insert the NULL value again:

insert into test_uq (col2) values (Null);

This time it will go through. If we examine the contents of the table:

COL1        COL2       COL3
 ----------- ---------- ----------
 1           abc        abc
 2           xyz        xyz
 3           NULL       3
 5           NULL       5

As you can see, we have allowed multiple NULL values now for COL2 and still maintained the uniqueness. The data is not corrupted either since we maintain the multiple Null values in that column and the dummy scheme that we used only put the dummy values in COL3 which is not going to be used in any of the queries.  That is why we did not just insert the values 3, 5 directly in COL2.  We can next try to insert the value “abc” again and see if that preserves our uniqueness criteria:

insert into test_uq (col2) values (‘abc’);

This time, we will get an error:

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object ‘dbo.test_uq’ with unique index ‘TEST_UQ_IND_1’.
The statement has been terminated.

So, using this work-around, one can preserve the same behavior as Oracle. This might be useful to you as well in case you are working on a project that requires conversion from Oracle to SQL Server or Oracle to DB2 LUW.

Solution 2:

There is one more solution for this problem and it does not include a computed column. Senior developers must have already figured out the solution – Yes, usage of a trigger. The solution will basically mean that we do not create the unique index but just a regular index and we create a an insert and update action trigger which checks whether the inserted value exists or not and handle the uniqueness that way essentially doing a rollback when there is an attempt to insert a duplicate value. The overhead of the invocation of the trigger for every insert and update in this case will be minimal because the trigger code will be checking for only one single value of the indexed column. The advantage is that you do not have another additional computed column and your structure remains exactly the same as in the case of Oracle.  This is how the trigger definition would look like:

/*Table Definition – same as before*/
CREATE TABLE TEST_UQ (COL1 INT IDENTITY(1,1) PRIMARY KEY, COL2 NVARCHAR(10) NULL)
GO

/*Non Unique index*/
CREATE INDEX TEST_UQ_IND_1 ON TEST_UQ (COL2)
GO

Now, let us try to insert these values:

insert into test_uq (col2) values (‘abc’);
insert into test_uq (col2) values (‘xyz’);
insert into test_uq (col2) values (Null);

/*Trigger Definition*/
create trigger dbo.test_allow_unique
on test_uq
for insert, update
AS
set nocount on

IF EXISTS(select 1
from   inserted as i
inner join dbo.test_uq as tu
on i.col2 = tu.col2
group  by tu.col2
having count(tu.col2) > 1)
BEGIN
ROLLBACK
RAISERROR(‘Uniqueness Criteria for COL2 got violated.’, 16, 1)
END
GO

/*Insert another Null value – this will go through – this is a nullable column and the index is a non-unique index*/
insert into test_uq (col2) values (Null);

/*Insert a value that should result in a violation as per the trigger logic*/
insert into test_uq (col2) values (‘xyz’);

Msg 50000, Level 16, State 1, Procedure test_allow_unique, Line 14
Uniqueness Criteria for COL2 got violated.
Msg 3609, Level 16, State 1, Line 1
The transaction ended in the trigger. The batch has been aborted.

So, the trigger approach works as well and as pointed out before – the hit is fairly small since the look up happens on COL2 which will have unique values only in it.  Just make sure that the trigger is always in an enabled state.