Foreign key with additional relationship constraint

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.

Damien_The_Unbeliever · Accepted Answer · 2014-08-08 07:45:11Z

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.

Collectives™ on Stack Overflow

Foreign key with additional relationship constraint

1 Answer 1

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Linked

Related