title: How do I have a check constraint that refers to another table?
body: <p>I have the following tables in a SQL Server 2008 db:</p>
  
  <ul>
  <li><p><strong>tblItem</strong>, which has an <strong>ItemID</strong> field;</p></li>
  <li><p><strong>tblGoodItem</strong>, which also has an ItemID field, and has a foreign key pointing to tblItem;</p></li>
  <li><p><strong>tblBadItem</strong>, which also has an ItemID field, and also has a foreign key pointing to tblItem.</p></li>
  </ul>
  
  <p>An item cannot be both a good item and a bad item; it must either be the one or the other. However, whether the item is good or bad, it must be an item.</p>
  
  <p>My question is this: <strong>how do I add a constraint to the ItemID fields in both tblGoodItem and tblBadItem so that an ItemID value cannot exist in both tables</strong>?</p>
  
  <p>I've read some replies in Stack Overflow on similar questions, and I'm thinking of this solution:</p>
  
  <ul>
  <li><p>Create a view <strong>vwItem</strong> which joins tblGoodItem on tblBadItem on ItemID.</p></li>
  <li><p>Write a UDF <strong>fnItem</strong> which does a query on vwItem to see how many records exist in the view.</p></li>
  <li><p>Have a constraint which calls fnItem and verifies that the value returned is 0.</p></li>
  </ul>
  
  <p>Is this best idea? Does anyone have a better idea?</p>