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    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.

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)
);

forum

Foreign key with additional relationship constraint

Anonymous User

1 Answers

Liked By