CODEWITHSUNDEEP
c#sql-server-2008-r2
Ruby
Ruby

Reputation: 969

Add Unique Key For Nullable Columns - SQL Server

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);

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

EDIT:

Followed @marc_s suggestion, here to create an unique key constraint. 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.

CS

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();

Upvotes: 2

Views: 2743

Answers (3)

marc_s
marc_s

Reputation: 754268

So you're creating a nullbuster column for each nullable column you want to index? Seems like overkill....

Wouldn't it be much easier to just create filtered indexes for those nullable columns, something like:

CREATE UNIQUE INDEX UK_Column1 ON tblBranch(column1) WHERE column1 IS NOT NULL;

and thus allow any number of NULL entries, while uniquely indexing the other values. That way, you don't need to add all those nullbuster columns just to make indexing possible...

Read more about filtered indexes:

Update: in order to set a parameter to NULL (and not an empty string), use this code:

if(string.IsNullOrEmpty(txtBranchCode.Text))
{
    cmd.Parameters.AddWithValue("@BranchCode", DBNull.Value);
}
else
{
    cmd.Parameters.AddWithValue("@BranchCode", txtBranchCode.Text);
}

Upvotes: 3

nvogel
nvogel

Reputation: 25526

I suggest you move column1 to a new table and make it unique and non-nullable. Reference the Branch table using a foreign key in the new table. Populate the new table only where you have a (non-null) value for column1.

Creating a new table is the surest way to support any dependencies on column1. A column that permits nulls is not a key column but column1 apparently should be. Putting that column into a new table also happens to be the only way to satisfy Normal Form and that ought to be the default approach unless you have a very compelling reason to "denormalise" column1 into the Branch table where it doesn't seem to belong.

Upvotes: 0

usr
usr

Reputation: 171178

Use the filtered index suggestion provided by marc_s. You say that you are still getting a unique index violation. What does that tell you? It tells you you are inserting a duplicate key with regards to the index definition. You think, you aren't, but you definitely are.

How to debug that? You look at what you are inserting. You look at gId and txtBranchCode.Text in the debugger. Also, you look at the error message, because it says:

The duplicate key value is ...

All of these clues lead you to find that txtBranchCode.Text is not null, but an empty string. Insert NULL instead.

Upvotes: 1

Related Questions