Method for properly constraining an address table

• bagofbirds-767347

  Ten Centuries

  Points: 1110

  More actions

  December 13, 2008 at 6:34 pm

  #216578

  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.

• RBarryYoung

  SSC Guru

  Points: 143329

  More actions

  December 13, 2008 at 7:21 pm

  #912149

  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]