Foreign Key With Additional Relationship Constraint

In SQL Server 2012, can I create a foreign key constraint that includes a restriction on which rows can be referenced based on other keys? Example: CREATE TABLE Client ( Id

Solution 1:

Create a superkey in Location:

CREATETABLE Location (
    Id           INTIDENTITYPRIMARY KEY,
    Description  NVARCHAR(200),
    ClientId     INTNOTNULL,

    FOREIGN KEY (ClientId) REFERENCES Client(Id),
    CONSTRAINT UQ_Location_Client_XRef UNIQUE (Id,ClientId)
);

And then use that as an additional, or as a replacement, for the foreign key in Defect:

CREATETABLE Defect (
    Id           INTIDENTITYPRIMARY KEY,
    Description  NVARCHAR(200),
    ClientId     INTNOTNULL,
    LocationId   INTNULL,

    FOREIGN KEY (ClientId) REFERENCES Client(Id),
    FOREIGN KEY (LocationId) REFERENCES Location(Id), --<-- Redundantconstraint FK_Defect_Location_Client_XRef FOREIGN KEY
         (LocationId,ClientId) REFERENCES Location(Id,ClientId)
);

It's a matter of taste whether you actually remove the redundant FK.