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

sql, sql-server, sql-server-2008-r2, unique-constraint

Solution

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.

Problem

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.

Original source