Method for properly constraining an address table

  • Hi everyone - i have the following 3 column table named User_Address with the columns:

    UserID int

    AddressID int

    IsPrimary bit

    Now the logic i need to enforce is this: for a given UserID a there can be an infinite number of records, but only one record per userID can be marked "IsPrimary"=1

    At first i thought i'd do a simple unique index on UserID,IsPrimary. But of course, this won't allow multiple userIDs with IsPrimary=0.

    I am thinking now i need some sort of check constraint, but i cannot figure it out. i've never done check constraints that check other rows. my next step would be a trigger but i really don't want to go there.

    Any ideas? i am thinking this can't be that unique a problem.

    Thanks.

  • Take the IsPrimary flag out of UserAddress and put a PrimaryAddressID column into the Users table.

    [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 2 posts - 1 through 1 (of 1 total)

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