Foreign key with additional relationship constrain

Question

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           INT IDENTITY PRIMARY KEY
    Description  NVARCHAR(200)
);

CREATE TABLE Location (
    Id           INT IDENTITY PRIMARY KEY,
    Description  NVARCHAR(200),
    ClientId     INT NOT NULL,

    FOREIGN KEY (ClientId) REFERENCES Client(Id)
);

CREATE TABLE Defect (
    Id           INT IDENTITY PRIMARY KEY,
    Description  NVARCHAR(200),
    ClientId     INT NOT NULL,
    LocationId   INT NULL,

    FOREIGN KEY (ClientId) REFERENCES Client(Id),
    FOREIGN KEY (LocationId) REFERENCES Location(Id)
);

I would like to constrain Defect.LocationId such that the related Location row must have the same ClientId as the Defect row.

In other words, a Location and Defect can only be related if they belong to the same Client.

Answer 1

Create a superkey in Location:

CREATE TABLE Location (
    Id           INT IDENTITY PRIMARY KEY,
    Description  NVARCHAR(200),
    ClientId     INT NOT NULL,

    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:

CREATE TABLE Defect (
    Id           INT IDENTITY PRIMARY KEY,
    Description  NVARCHAR(200),
    ClientId     INT NOT NULL,
    LocationId   INT NULL,

    FOREIGN KEY (ClientId) REFERENCES Client(Id),
    FOREIGN KEY (LocationId) REFERENCES Location(Id), --<-- Redundant
    constraint 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.