Post reply

Can this validation rule be done with a Constraint?

  • May 26, 2008 at 9:11 am

    #190999

    Hello all,

    I'd appreciate some help on a validation rule. It seems to me this scenario is probably quite common, but I have spent days searching and can't come up with the right combination of keywords to find the information I need.

    Suppose I have the following fields in a junction table:

    EmployeeID int

    EmployeeEmailID int

    IsPrimary bit

    I'd like to, if possible, enforce the following rule at table level:

    IsPrimary may be set to True for only one occurrence of any EmployeeID.

    Is this possible with a Check Constraint? If so, can someone please show me how to construct it?

    If not, is a Trigger the most viable solution?

    Many thanks

  • May 26, 2008 at 9:58 am

    #820222

    I don't believe it's possible with a Check Constraint. Pretty sure a trigger is the only way to do this one.

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

  • May 26, 2008 at 10:28 am

    #820233

    I agree with Jeff, and just to add to it: From a relational design standpoint, this is better implemented as a PrimaryEmailID attribute (column) of the Employee table where its unique relation to the EmployeeID is implicit, rather than as an attribute of the mapping (junctuion) table where its relationship is obtuse.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • May 26, 2008 at 11:04 am

    #820240

    Thank you, Jeff. I suspected as much.

    And thanks very much, RBarryYoung. I am implementing your suggestion, as it certainly makes the best sense from a design perspective. Since I had a junction or mapping table in the mix, I fell into the trap of taking the easy(?) way out, using it for something other than mapping multiple records.

    Now I have no need for an additional Check Constraint or Trigger.

    I appreciate the heads up. At my age, it's more than helpful - it's necessary, sometimes. 😀

  • May 26, 2008 at 12:06 pm

    #820252

    glad I could help!

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 5 posts - 1 through 4 (of 4 total)

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