How to design an optional unique constraint

Question

How to design an optional unique constraint

Stephanie Giovannini

SSCertifiable

Points: 7422
More actions
June 26, 2008 at 10:52 am

#189624

I always remember that unique constraints allow nulls, but forget that they only allow one null (assuming the constraint is on just one column).
I have a table in which a column value must be unique, if it exists. If the value does not exist, then it can be null (unlimited number of nulls).
I started by assigning a unique constraint to a nullable column, but, of course, that doesn't work when I add the data with numerous null values.
What is a good alternative? I can think of a few:
Insert/update triggers
Additional table
I'm leaning toward triggers. The additional table would have only two columns (the id relating it back to the parent table and the non-null unique code), and many queries would involve outer joins to that table.
Is there a standard way to implement this type of constraint?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 1

Stephanie Giovannini (6/26/2008)
I always remember that unique constraints allow nulls, but forget that they only allow one null (assuming the constraint is on just one column).
I have a table in which a column value must be unique, if it exists. If the value does not exist, then it can be null (unlimited number of nulls).
I started by assigning a unique constraint to a nullable column, but, of course, that doesn't work when I add the data with numerous null values.
What is a good alternative? I can think of a few:
Insert/update triggers
Additional table
I'm leaning toward triggers. The additional table would have only two columns (the id relating it back to the parent table and the non-null unique code), and many queries would involve outer joins to that table.
Is there a standard way to implement this type of constraint?

Second idea is probably the best with a little help... foreign key to a definition table of allowable values...

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stephanie Giovannini SSCertifiable Points: 7422 More actions · Answer 2

Hmmm... I suppose the second table is the best way to do it, if I'm allowed to do so. At least I now have ammunition.

Mark Cowne One Orange Chip Points: 26952 More actions · Answer 3

You can use indexed views for this

http://www.sqlservercentral.com/Forums/Topic471378-338-1.aspx#bm471408

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 4

Stephanie Giovannini (6/27/2008)
Hmmm... I suppose the second table is the best way to do it, if I'm allowed to do so. At least I now have ammunition.

Sorry, Stephanie... I have to remember... "Optional" and "Unique". 2nd Table with FK would be way too high maintenance. Bad suggestion on my part... sorry.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Stephanie Giovannini SSCertifiable Points: 7422 More actions · Answer 5

Jeff Moden (6/27/2008)
Stephanie Giovannini (6/27/2008)
Hmmm... I suppose the second table is the best way to do it, if I'm allowed to do so. At least I now have ammunition.
Sorry, Stephanie... I have to remember... "Optional" and "Unique". 2nd Table with FK would be way too high maintenance. Bad suggestion on my part... sorry.

OK, then. My plan now is:

- One table

- Optional, unique column is nullable and indexed

- Uniqueness enforced by insert/update trigger

Actually, the two tables make queries sort of awkward. I already made the changes to move the optional code into a separate table. I'll just change it back.

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 6

So, how did you solve the problem of both being optional AND unique?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)