具有附加关系约束的外键 [英] Foreign key with additional relationship constraint

问题描述

在SQL Server 2012中，我可以创建一个外键约束，该约束包含对哪些行可以基于其他键进行引用的限制吗？

示例：

  CREATE TABLE客户端（
 Id INT IDENTITY PRIMARY KEY 
描述NVARCHAR（200）
）; 
 
 CREATE TABLE位置（
 Id INT IDENTITY PRIMARY KEY，
描述NVARCHAR（200），
 ClientId INT NOT NULL，
 
 FOREIGN KEY（ClientId）REFERENCES客户端（Id）
）; 
 
 CREATE TABLE缺陷（
 Id INT IDENTITY PRIMARY KEY，
描述NVARCHAR（200），
 ClientId INT NOT NULL，
 LocationId INT NULL，
 
 FOREIGN KEY（ClientId）REFERENCES客户端（Id），
 FOREIGN KEY（LocationId）参考位置（Id）
）; 
  

我想约束 Defect.LocationId 使相关的 Location 行必须与 Defect ClientId 换句话说，一个位置和缺陷只能属于相同的客户端。

解决方案

在位置中创建超级键：
$ b

  CREATE TABLE Location （
 Id INT IDENTITY PRIMARY KEY，
描述NVARCHAR（200）
 ClientId INT NOT NULL 
 $ b FOREIGN KEY（ClientId）REFERENCES Client（Id），
 CONSTRAINT UQ_Location_Client_XRef UNIQUE（Id，ClientId）
）; 
  

然后用它作为额外的或替代的<

$ b $ pre $ CREATE TABLE Defect（
Id INT IDENTITY PRIMARY KEY，
描述NVARCHAR（200）
ClientId INT NOT NULL $ b $ LocationId INT NULL
$ b FOREIGN KEY（ClientId）REFERENCES客户端（Id），
FOREIGN KEY（LocationId）REFERENCES位置（Id）， - < - 冗余
约束FK_Defect_Location_Client_XRef FOREIGN KEY
（LocationId，ClientId）参考位置（Id，ClientId）
）;

您是否真的删除了多余的FK是个问题。

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

这篇关于具有附加关系约束的外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！