How to add a unique constraint using a column from another table?

I have 3 tables in SQL Server 2008 R2 that look like these:

A COMPANY may have many LSPs. An LSP may have many SERVICEs.

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.

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".

I need something like this:

alter table "SERVICE" 
add constraint "SERVICE_Index01" 
unique ("COMPANY_ID", "SERVICE_CODE") 

But (obviously) this fails because the COMPANY_ID column is not in the SERVICE table.

Thanks in advance for any help.

You could use an indexed view as an external constraint:

CREATE VIEW dbo.CompanyServices
WITH SCHEMABINDING
AS
SELECT
  c.COMPANY_ID,
  s.SERVICE_CODE
FROM       dbo.COMPANY c
INNER JOIN dbo.LSP     l ON c.COMPANY_ID = l.COMPANY_ID
INNER JOIN dbo.SERVICE s ON l.LSP_ID     = s.LSP_ID
GO

CREATE UNIQUE CLUSTERED INDEX UQ_CompanyServices
ON dbo.CompanyServices (COMPANY_ID, SERVICE_CODE);

The index will make sure there's no duplicates of (COMPANY_ID, SERVICE_CODE) in your data.