title: How to add a unique constraint using a column from another table?
body: <p>I have 3 tables in SQL Server 2008 R2 that look like these:</p>
  
  <p><img src="https://i.stack.imgur.com/IDFG4.gif" alt="DB Model"></p>
  
  <p>A COMPANY may have many LSPs. An LSP may have many SERVICEs.</p>
  
  <p>And I need to make sure that SERVICE_CODE uniquely identifies a SERVICE record within a COMPANY. In other words, COMPANY_ID + SERVICE_CODE should uniquely identify a SERVICE record in the entire system.</p>
  
  <p>For example: COMPANY-A may NOT have 2 services (with 2 different SERVICE_IDs) with the same SERVICE_CODE. But COMPANY-A and COMPANY-B may both have 2 separate SERVICES (again, with different SERVICE_IDs) with SERVICE_CODE = "PREMIUM".</p>
  
  <p>I need something like this:</p>
  
  <pre><code>alter table "SERVICE" 
  add constraint "SERVICE_Index01" 
  unique ("COMPANY_ID", "SERVICE_CODE") 
  </code></pre>
  
  <p>But (obviously) this fails because the COMPANY_ID column is not in the SERVICE table.</p>
  
  <p>Thanks in advance for any help.</p>