title: Complex Check Constraint?
body: <p>I have a customers table that links to an addresses table through a middle CustomerAddress table. This means that a customer can have many addresses and an address can have many customers. (This is neccessary due to the fact that we deal with spouses and children as seperate customers, and each can have delivery, work, billing and other addresses).</p>
  
  <p>I want a customer to be able to specify a preferred address. </p>
  
  <p>My thought was to create a new column in the customers table that links to a CustomerAddress record.</p>
  
  <p>My problem is- how can I ensure that the selected preferred address is one of that customers addresses?</p>
  
  <p>My thought was to put a Check constraint on the customers.preferredAddress field that checks the given CustomerAddress to see if that records customer ID matches the customer being updated.</p>
  
  <p>Is this possible? I have only ever used Check constraints to check simple stuff like (Value > 0) etc.</p>
  
  <p>Thanks for your help </p>