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;