Unique Column that Allows Null Value in SQL

By | August 25, 2017

Well its been quite sometime now since our last post, was so busy with lots of things now that I manage three teams, Development, Operations and Project Management.  My programming activity drastically went down as I do a lot of juggling between three roles plus managing them, imagine a portfolio of 63 projects last year with 3 Project Managers, 3 Developers and 3 Engineers, it was quite a task but yes I managed to deliver mostly on time.  

Today I had a chance in solving some issue and yes I still do learn new things once in a while.  The issue that I was presented was a duplicating SKU on a database which comes from a bug on the UI side.  While it can be fixed on the UI I wanted to prevent it from happening on the Database side hence I was thinking of making the column unique but still allow null since not all products in the Database has SKU yet, still needs provisioning.


At first I thought this is not possible but after playing around with SQL Constraints I managed to to it by adding a WHERE clause so the constraint only applies on the records which is not NULL or even a certain condition.  This is the first time I tried this and it works, I know its not new but yes I just like it that you can use where clause when creating INDEXES.  So in my instance I have a Product Table where there is a column called SKU, I want that SKU to contain Unique Alphanumeric Characters but still allow NULL, and this is how I did it.

CREATE UNIQUE NONCLUSTERED INDEX IDX_StockKeepingUnit_NotNull
ON Products(StockKeepingUnit)
WHERE StockKeepingUnit IS NOT NULL;

Leave a Reply