Multiple NULL values in UNIQUE constraint

A unique index guarantees that the index key contains no duplicate values and therefore every row in the table is in some way unique. Specifying a unique index makes sense only when uniqueness is a characteristic of the data itself. With multicolumn unique indexes, the index guarantees that each combination of values in the index is unique. Both clustered and nonclustered index can be unique. We can create a unique clustered index and multiple nonclustered indexes on the same table.

Below are the considerations when designing UNIQUE index -

A UNIQUE index, UNIQUE constraint or PRIMARY KEY constraint cannot be created if duplicate key values exist in the data.
If the data is unique and you want uniqueness enforced, creating a unique index of a non-unique index on the same consideration of columns provides additonal information for the query optimizer that can produce more efficient plans.
A unique nonclustered index can contain included non-key columns.

From the above explanation it is clearly understood that unique constraint does not allow duplicate values and the same is true with NULL values as well. When two rows are inserted with NULL values into UNIQUE index column, SQL Server throws an error saying that two NULL values are same and are duplicates.

SQL Server's implementation of UNIQUE index is not ANSI-complaint. According to the ANSI standards SQL:92, SQL:1999, and SQL:2003, a UNIQUE constraint should not allow duplicate non-NULL values, but allow multiple NULL values.

NULL is not equal to NULL, NULL is nothing, unknown or undefined. When two NULL values are different, why multiple NULL values are not allowed in a column defined as UNIQUE constraint. It is probably SQL Server's implementation of a UNIQUE index is different than other database programs and also to maintain data integrity.

But there are few solutions to this problem, which are shown below.

Solution 1

As mentioned above if we insert multiple NULL values to maintain data integrity SQL Server will throw an error, so the work-around would be as follows:

CREATE TABLE T1 
(
	ID INT IDENTITY(1,1) PRIMARY KEY, 
	TITLE VARCHAR(20), 
	TITLE_COMP AS CASE WHEN TITLE IS NULL THEN CAST(ID AS VARCHAR(20)) ELSE TITLE END)
GO

Note that ID column is defined as PRIMARY KEY constraint, TITLE column will hold the titles of the books which we want all unique values and multiple NULL values to be inserted into this column, to overcome this problem we had created the third column named TITLE_COMP which is a computed column which gets its value from TITLE column when inserting non-NULL value and when a NULL value is inserted into TITLE column then it gets the value from ID column and casting the value to the same datatype as TITLE. So, we will be creating the UNIQUE constraint on TITLE_COMP column which holds the unique value.

Create UNIQUE index on TITLE_COMP column now:

CREATE UNIQUE INDEX UQ_T1_1 
	ON T1 (TITLE_COMP)
GO

Insert few rows into this table:

INSERT INTO T1 (TITLE) VALUES ('Ravi');
INSERT INTO T1 (TITLE) VALUES ('Amit');
INSERT INTO T1 (TITLE) VALUES (NULL);

The data inserted into the table as below:

ID	TITLE	TITLE_COMP
1	Ravi		Ravi
2	Amit		Amit
3	NULL		3

Now see the TITLE_COMP column has the value from ID column when a NULL value is inserted into TITLE column as it was defined while creating the table. This way we can make sure that no NULL values are inserted into TITLE_COMP column and at the same time we can insert multiple NULL values into TITLE column as per the requirement. This is fine until the TITLE column is of VARCHAR datatype, if you want the uniqueness should be defined on a INT datatype column (in our case it is TITLE column), then there will be a conflict with ID column value and TITLE column value when stored in TITLE_COMP column. By doing a small change we can achieve the uniqueness. Change the CASE statement in the computation of TITLE_COMP column definition as given in the below CREATE TABLE statement:

CREATE TABLE T1 
(
	ID INT IDENTITY(1,1) PRIMARY KEY, 
	TITLE VARCHAR(20), 
	TITLE_COMP AS CASE WHEN TITLE IS NULL THEN -1*ID ELSE TITLE END)
GO

Now, insert one more NULL value into TITLE column and see if you get an error.

INSERT INTO T1 (TITLE) VALUES (NULL);

No error raised after executing the above query, now check if the uniqueness is retained by inserting the existing value into TITLE column.

INSERT INTO T1 (TITLE) VALUES ('Ravi');

SQL Server throws the below error when executing the above statement as there is a value 'Ravi' in TITLE column which is being inserted into TITLE_COMP and voilating the uniqueness.

Msg 2601, Level 14, State 1, Line 1
Cannot insert duplicate key row in object 'dbo.T1' with unique index 'UQ_T1_1'.
The statement has been terminated.

If we insert a different non-NULL value in TITLE column which is not existing in the table.

INSERT INTO T1 (TITLE) VALUES ('Ashok');

This way we can meet the requirement of adding multiple NULL values and maintaining the data integrity between the non-NULL values. But to achieve this, we need to sacrifice the space utilised by the additional column (i.e., TITLE_COMP) holding the duplicate information.

There are other alternatives also as work arounds to this problem which is by creating a schema-bound VIEW on the TABLE where you want unique values to be inserted and create a UNIQUE index on the VIEW. Please see the below sample script which works as second solution.

Solution 2

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE TABLE T1 (ID INT, TITLE VARCHAR(20))
GO

CREATE VIEW V1
WITH SCHEMABINDING
AS
    SELECT ID, TITLE
    FROM dbo.T1
    WHERE ID IS NOT NULL
GO

--Create an UNIQUE clustered index on the view.
CREATE UNIQUE CLUSTERED INDEX CI_V1_ID 
    ON dbo.v1 (id) WITH (IGNORE_DUP_KEY = OFF);
GO

By querying the VIEW V1 we are only retrieving the non-NULL values leaving the NULL values in the base table.

The above two solutions would work on any version of SQL, when it comes to SQL Server 2008 we have another solution by using filtered index which can replace the second solution above, by simply creating the below UNIQUE filtered index.

Solution 3

CREATE UNIQUE NONCLUSTERED INDEX ON dbo.T1(TITLE)
WHERE TITLE IS NOT NULL;
GO

Hope there will be a fix for this problem from Microsoft in the future SQL Server versions.

If you have any queries on this post, please reply to this post.

Thanks,

Ravi.

7/4/2012 7:10:08 AM Catalina said:

Thank you for your help. I used the second apcaorph to get past configuring MySQL to have root access from remote machine. Here is what I did: From command line, log in mysql (mysql -h localhost -u root -p)>)mysql> use mysqlmysql> ALTER TABLE `user` CHANGE COLUMN `authentication_string` `authentication_string` TEXT NULL COLLATE 'utf8_bin' AFTER `plugin`; This operation should succeed.Configure MySQL again (with root access allowed for remote machine checked) and the operation should succeed this time.

12/14/2012 11:58:41 AM Abrar Ahmad said:

Thank you very much for the explanation and examples. Solution 3 was the best option for me. I did need to put in the index name.

Here's the example with the index name:

1    CREATE UNIQUE NONCLUSTERED INDEX idx_unique_title ON dbo.T1(TITLE)
2    WHERE TITLE IS NOT NULL;
3    GO

Multiple NULL values in UNIQUE constraint

Multiple NULL values in UNIQUE constraint

Author

Comments (2)

Email Subscriptions

Most Recent Articles