Column with unique elements but allowing multiple NULLs

Jun 29, 2010 at 3:11 PM

I have read that it is a standard practice in Microsoft SQL Server development to "create a calculated column that is based on the column that allows nulls, and then define a UNIQUE constraint on the calculated column." This allows for a developer to create a column that maintains the unique constraint for valid entries but also allows for multiple "empty" entries using NULL instances (reference http://support.microsoft.com/kb/322002). I have attempted to perform these actions using nHydrate but I am not able to enforce the "isUnique" constraint on a calculated column. What is the reasoning behind this limitation and is their a workaround for this? Thanks!

Coordinator
Jul 1, 2010 at 5:42 AM

There is no good reason for it. I have made changes to the code to allow a unique index to be created on a calculated column and this will in the next build.

Coordinator
Jul 6, 2010 at 2:05 AM

Please download the latest build 3.6.0.165 to see this change.

 

Jul 10, 2010 at 9:17 PM

Thanks for making this change so quickly. I will give it a shot and let you know if I run into any issues. Keep up the great work! This is a real time saver...

Coordinator
Jul 10, 2010 at 9:36 PM

There will be another push Sunday night (July 11) with the new Entity Framework DAL BETA in it. This might interest you, if you are using EF for anything. We basically now generate EF with database tracking. There is also some convenience functionality that EF just does not provide.