SQL Journey

Journey of SQL Server & Microsoft Business Intelligence

UNIQUE Key Constraint with Multiple NULL values in SQL Server

Posted by Prashant on June 12, 2010


This was a really interesting situation when we development team members were discussing over this topic. It was a situation where it was not possible to allow multiple NULL values into a column with UNIQUE Key defined in a table. Here I will give the problem first then we will go with the alternate solution for this.

Problem:

In SQL Server it is not allowed to insert multiple NULL values into a UNIQUE Key column. Below is the situation:

--Create employee table
CREATE TABLE EMPLOYEE
(EMPLOYEEID INT NOT NULL
CONSTRAINT PK_EMPLOYEE PRIMARY KEY CLUSTERED
,FNAME VARCHAR(100)
,LNAME VARCHAR(100)
,UNIQUEID INT UNIQUE)
GO
--Insert data values (used row constructor)
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME, UNIQUEID)VALUES
(1,'RAHUL','MISHRA','112342115'),
(2,'SHEEL','KURANI','1725421455'),
(3,'SHEETAL','BAJAJ','1423721455'),
(4,'KAUSHIK','NARANG','1123721955'),
(5,'ADRIAN','THOULISS','1452342145')
GO
--Insert data value with NULL values to UNIQUE key column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(6,'SAUGAT','KIOHLI')
--See below the result
SELECT EMPLOYEEID, FNAME, LNAME, UNIQUEID FROM EMPLOYEE
GO

When I try to enter another record with NULL value to UNIQUEID column, it does not allow me to do that and throws an error.

--Insert data value with NULL values to UNIQUE key column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(7,'VAID','GURU')

--ERROR MESSAGE
--Msg 2627, Level 14, State 1, Line 2</pre>
--Violation of UNIQUE KEY constraint 'UQ__EMPLOYEE__04FF5B33023D5A04'.
--Cannot insert duplicate key in object 'dbo.EMPLOYEE'.
--The statement has been terminated.
Solution:
Here is the alternate solution for the above situation. Instead of define an UNIQUE Key to UNIQUEID column I will create another COMPUTED Column with UNIQUE Key. See how it is done:
--ALTERNATE SOLUTION
--Drop the UNIQUE key
ALTER TABLE EMPLOYEE
DROP CONSTRAINT UQ__EMPLOYEE__04FF5B33023D5A04

--Add a computed column with UNIQUE key
ALTER TABLE EMPLOYEE
ADD ALTUNIQUEID AS CASE WHEN UNIQUEID IS NULL THEN EMPLOYEEID ELSE UNIQUEID END
GO
ALTER TABLE EMPLOYEE
ADD CONSTRAINT UKey_QNIQUEID UNIQUE (ALTUNIQUEID)
GO

--Now insert multiple records with NULL to UNIQUEID column
INSERT INTO EMPLOYEE(EMPLOYEEID, FNAME, LNAME)
VALUES(7,'VAID','GURU'),(8,'TARUN','KISHORE')
GO
--See below the result
SELECT * FROM EMPLOYEE
GO

Your valuable comments are most appreciated.

2 Responses to “UNIQUE Key Constraint with Multiple NULL values in SQL Server”

  1. guleriahp said

    we can also create nonclustered unque index on column with filter
    as given below

    create unique nonclustered index index_name on dbo.table_name(column_name)
    where column_name is not null;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: