Create a unique constraint/index, that considers a second table

Question

In my SQL Server database, I want to create a unique constraint, that considers a second table. My table ChargeCarrier_Storage contains the two ID fields ID_ChargeCarrier and ID_Storage. My Storage table contains the two fields ID and ID_StorageType. The constraint should be, that for storage type 1, there should be only one possible entry in the ChargeCarrier_Storage table. For any other storage type, there can be an infinite number of entries.

My first approach was to create a non-clustered index on the ChargeCarrier_Storage table, but that obviously doesn't work, because, as it seems, you can't use joins there.

CREATE NONCLUSTERED INDEX UQX_OnePickingStoragePerCarrier  
    ON dbo.ChargeCarrier_Storage ccs (ID_ChargeCarrier, ID_Storage)  
    INNER JOIN dbo.Storage s ON s.ID = ccs.ID_Storage
    WHERE s.ID_StorageType = 1;  
GO

Is there some easy way to make this work?

Answer 1

You can't do this on the table, you'll need to use VIEW and create the index on there:

USE Sandbox;
GO

--Create sample tables
CREATE TABLE dbo.ChargeCarrier_Storage (ID_ChargeCarrier int,
                                        ID_Storage int);
CREATE TABLE dbo.Storage (ID int,
                          ID_StorageType int);
GO
--Create Schema bound View
CREATE VIEW dbo.OnePickingStoragePerCarrier WITH SCHEMABINDING
AS
    SELECT ccs.ID_ChargeCarrier,
           s.ID AS ID_Storage
    FROM dbo.ChargeCarrier_Storage ccs
         INNER JOIN dbo.Storage s ON s.ID = ccs.ID_Storage
    WHERE s.ID_StorageType = 1;  
GO
--Create unique (clustered) index
CREATE UNIQUE CLUSTERED INDEX UQX_OnePickingStoragePerCarrier ON OnePickingStoragePerCarrier (ID_ChargeCarrier,ID_Storage);
GO

--Sample data in Storage
INSERT INTO dbo.Storage (ID,
                         ID_StorageType)
VALUES(1,1),
      (2,1),
      (3,2);
GO

--Tests
INSERT INTO dbo.ChargeCarrier_Storage (ID_ChargeCarrier,
                                       ID_Storage)
VALUES(1,1); --Works
GO

INSERT INTO dbo.ChargeCarrier_Storage (ID_ChargeCarrier,
                                       ID_Storage)
VALUES(2,1); --Works
GO

INSERT INTO dbo.ChargeCarrier_Storage (ID_ChargeCarrier,
                                       ID_Storage)
VALUES(1,2); --Works
GO

INSERT INTO dbo.ChargeCarrier_Storage (ID_ChargeCarrier,
                                       ID_Storage)
VALUES(2,1); --fails due to duplicate
GO
INSERT INTO dbo.ChargeCarrier_Storage (ID_ChargeCarrier,
                                       ID_Storage)
VALUES(1,3); --works.

INSERT INTO dbo.ChargeCarrier_Storage (ID_ChargeCarrier,
                                       ID_Storage)
VALUES(1,3); --works, as not Storage ID 1, so duplicate allowed
GO

GO

DROP VIEW dbo.OnePickingStoragePerCarrier;
DROP TABLE dbo.Storage;
DROP TABLE dbo.ChargeCarrier_Storage;