I just want to add Unique constraint on a column that allows NULL. It is as simple as that. However, things that seems very simple turn out to be, well, not so simple sometimes. ANSI SQL standards describe the simple requirement as a UNIQUE constraint must not allow duplication of a non-NULL value but accept multiple NULL values.
As it turned out, in the Microsoft world of SQL Server, this is not possible. If you add UNIQUE constraint on a column, SQL Server considers NULLS as a value as well and does not allow duplication of NULLs in the UNIQUE column.
After spending some time on the internet, I found this solution. It worked for me, and hence I am saving it in my blog for the benefit of everyone else, including myself. So here it goes:
In SQL Server 2008 and later, you can add the where clause on an index to filter out the values you don't want the index to consider.
CREATE UNIQUE NONCLUSTERED INDEX idx_unq_ref_non_null ON MY_TABLE_NAME(my_column_name) WHERE my_column_name IS NOT NULL
As a result, the index will be applied to only those columns which have a non-NULL value as you have explicitly excluded the NULL values from your index.
For the earlier versions of SQL Server, the only way around is to create a view with the same WHERE clause as above so that your view will always have only the non-NULL values. Once the view is created then use the view for your ISERTs and UPDATEs instead of the table. It will be something like this.
#---Step:1 Create a View excluding nulls----- CREATE VIEW VW_MY_TABLE_NAME AS SELECT * FROM MY_TABLE_NAME WHERE my_column_name IS NOT NULL #---Step:2 Create unique index on column----- CREATE UNIQUE INDEX idx_unique_column ON MY_TABLE_NAME(my_column_name)
Once the view is created with the UNIQUE index, then you need to update the code using that database to use the view that you have just created instead of that underlying table.
If for whatever reason it is not feasible to make changes to the code then the alternative approach is to add a trigger on your table to force every insert to go through the view:
CREATE TRIGGER trg_my_table_insert
ON MY_TABLE_NAME
INSTEAD OF INSERT
AS
BEGIN
INSERT INTO VW_MY_TABLE_NAME
SELECT * FROM inserted
END
This simple trigger is intercepting the INSERT statement and using the view to insert the record. As a result your view will check the uniqueness of the value using the index on the view column. Note that the NULLs are excluded from the view and as a result the UNIQUE constraint will not come into effect if a NULL is inserted in that column.
I hope this will be a good starting point to solve a similar issue.
