none
How to create a constraint like this:

    Question

  • 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

    Wednesday, November 08, 2006 4:42 PM

Answers

  • 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.

    Wednesday, November 08, 2006 5:25 PM

All replies

  • 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.

    Wednesday, November 08, 2006 5:25 PM
  • Thanks for the answer, this should do the trick.

    Wednesday, November 08, 2006 5:48 PM