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

Question

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.

Answer 1

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.

Answer 2

Is each company limited to a single LSP? Is Service_Code unique (or could there be two service codes "PREMIUM" with different Service_IDs)?

CREATE TABLE dbo.Company
(
  CompanyID INT PRIMARY KEY
  -- , ...
);

CREATE TABLE dbo.LSP
(
  LSPID      INT PRIMARY KEY, 
  CompanyID  INT FOREIGN KEY REFERENCES dbo.Company(CompanyID) -- UNIQUE?
  -- , ...
);

CREATE TABLE dbo.Service
(
  ServiceID    INT PRIMARY KEY
  -- , ...
);

CREATE TABLE dbo.LSP_Service
(
  LSPID        INT FOREIGN KEY REFERENCES dbo.LSP(LSPID), 
  ServiceID    INT FOREIGN KEY REFERENCES dbo.Service(ServiceID), 
  PRIMARY KEY (LSPID, ServiceID)
);