How to create a constraint like this:

Hi,

Suppose the following table definition in Sql Server 2005,

create table CompanySymbol
CompanyId int,
SymbolId int,
IsPrimarySymbol bit

Primary Key (CompanyId, SymbolId)

How can I create a constraint which wil ensure that IsPrimarySymbol will be set to 1(true) only once per CompanyId while allowing it to be set to 0 an unlimited amount of time per CompanyId. i.e.:

CompanyId SymbolId IsPrimarySymbol
--
1 1 1 (IsPrimarySymbol to 1 for CompanyId 1)
1 2 0
1 3 0
2 1 1 (IsPrimarySymbol to 1 for CompanyId 2)
2 2 0
3 1 0
4 1 1 (IsPrimarySymbol to 1 for CompanyId 4)
4 2 0
4 3 0

Thanks

Your best bet is to use a trigger to enforce this rule. It can be coded to either reject any Insert/Update that leaves the table with two rows flagged as IsPrimarySymbol. Alternatively, it could be coded to zero out all but the latest record set to IsPrimarySymbol. Be sure to consider Inserts and Updates of more than one record at a time.

It can be accomplished with a Constraint, but you need to also add a computed column.

create table CompanySymbol
CompanyId int,
SymbolId int,
IsPrimarySymbol bit
Guard as Case when IsPrimarySymbol = 1 Then 0 else SymbolId End

Now you can create a Unique Constraint( CompanyId, Guard )

My vote is for the trigger.

|||

Thanks for the answer, this should do the trick.