title: How do I check constraints between two tables when inserting into a third table that references the other two tables?
body: <p>Consider this example schema:</p>
  
  <pre><code>Customer ( int CustomerId pk, .... )
  
  Employee ( int EmployeeId pk,
             int CustomerId references Customer.CustomerId, .... )
  
  WorkItem ( int WorkItemId pk,
             int CustomerId references Customer.CustomerId,
             null int EmployeeId references Employee.EmployeeId, .... )
  </code></pre>
  
  <p>Basically, three tables:</p>
  
  <ul>
  <li>A customer table with a primary key and some additional columns</li>
  <li>A employee table with a primary key, a foreign key constraint reference to the customer tables primary key, representing an employee of the customer.</li>
  <li>A work item table, which stores work done for the customer, and also info about the specific employee who the work was performed for.</li>
  </ul>
  
  <p>My question is. How do I, on a database level, test if an employee is actually associated with a customer, when adding new work items.</p>
  
  <p>If for example Scott (employee) works at Microsoft (customer), and Jeff (employee) works at StackOverflow (customer), how do I prevent somebody from adding a work item into the database, with customer = Microsoft, and employee = Jeff, which do not make sense?</p>
  
  <p>Can I do it with check constraints or foreign keys or do I need a trigger to test for it manually?</p>
  
  <p>Should mention that I use SQL Server 2008.</p>
  
  <p><strong>UPDATE:</strong> I should add that WorkItem.EmployeeId can be null.</p>
  
  <p>Thanks, Egil.</p>