FOREIGN KEY WITH ADDITIONAL RELATIONSHIP CONSTRAINT

jacob rasel

Total Post:88

Points:616
Posted by  jacob rasel
 977  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:51

    Points:359
    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.

      Modified On Apr-08-2018 11:45:23 PM

Answer

NEWSLETTER

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