Home > DeveloperSection > Forums > Foreign key with additional relationship constraint
jacob rasel
jacob rasel

Total Post:88

Points:616
Posted on    January-21-2015 10:27 PM

 MSSQL Server SQL Server 
Ratings:


 1 Reply(s)
 484  View(s)
Rate this:

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.



Hugh Jackman
Hugh Jackman

Total Post:52

Points:366
Posted on    January-21-2015 10:57 PM

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.


Don't want to miss updates? Please click the below button!

Follow MindStick