unique constraint based on value in a column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Other SQL Server Topics (2005)

unique constraint based on value in a column

Author

Topic

averhe
Starting Member

3 Posts

Posted - 2007-11-23 : 04:21:14

I don't immediately find if this is possible but hope someone can give me an answer:
is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?

Example: table (tableid, instancetype, instancename, ..)
instancetype can be A or B
if it is A then instancename must be unique
but for B instancename is not unique as these are copies from A

only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..

are there other solutions to make a constraint like this ?

Aeneas.

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-11-23 : 11:46:16

Here's a solution:

Create a computed column like this

ALTER TABLE yourtable
ADD MakeInstanceUnique AS CASE instancetype 
    WHEN 'A' THEN NULL
    WHEN 'B' THEN tableid
    END

Then create a unique index on instancetype, instancename, MakeInstanceUnique.

I assumed that tableid is the primary key for the table, so what you're doing is adding the primary value to the index when the instancetype is B, so that values are unique when type is B, but the NULLs when instancetype is A mean that the names have to be unique on their own in that case.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources