Using sql server 2008 R2 and would like to apply unique key constraint to nullable columns. Tried the following code.
CREATE UNIQUE INDEX UK_BranchCode ON tblBranch(BranchCode) WHERE BranchCode IS NOT NULL;
The target column is BranchCode. Now, there is one record in the table that has NULL for BranchCode. When I try to insert a new record, with Null BranchCode, from c#, it gives an error saying - Cannot insert duplicate key for unique index UK_BranchCode. But, when I manually insert a record in the database, it does accept null values. Where am I going wrong.
Guid gId = Guid.NewGuid();
cmd = new sqlcommand("insert into tblBranch(BranchId,BranchCode)
values(@BranchId,@BranchCode)",con);
cmd.Parameters.AddWithValue("@BranchId",gId);
cmd.Parameters.AddWithValue("@BranchCode",txtBranchCode.Text);//empty text here
con.Open();
cmd.ExecuteNonQuery();
cmd.Close();