Add Unique Key For Nullable Columns - SQL Server

Posted by Ruby on Stack Overflow See other posts from Stack Overflow or by Ruby
Published on 2013-11-03T07:06:54Z Indexed on 2013/11/03 9:53 UTC
Read the original article Hit count: 170

Filed under:

I'm using sql server 2008 R2 and would like to apply unique key constraint to nullable columns. This code works good, but if I have multiple columns to add this rule to, it would generate as many 'nullbuster' columns.

ALTER TABLE tblBranch
ADD nullbuster AS (CASE WHEN column1 IS NULL THEN BranchID ELSE NULL END);
CREATE UNIQUE INDEX UK_Column1 ON tblBranch(column1,nullbuster);

tblBranch is the table name, nullbuster would be the new column name, BranchId is the Primary key column of the target table, and Column1 is the column name of the target column.

Is there any way that I could achieve the goal without generating new columns.

© Stack Overflow or respective owner

Related posts about sql-server-2008-r2