FOREIGN KEY WITH ADDITIONAL RELATIONSHIP CONSTRAINT

jacob rasel

Total Post:88

Points:616
Posted by  jacob rasel
 766  View(s)
Ratings:
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.

  1. Hugh Jackman

    Post:52

    Points:366
    Re: Foreign key with additional relationship constraint

    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.

Answer

NEWSLETTER

Enter your email address here always to be updated. We promise not to spam!