带有附加关系约束的外键

Question

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

的例子:

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)
);

我想约束Defect.LocationId，使相关的Location行必须具有与Defect行相同的ClientId。

换句话说，Location和Defect只有在属于同一个Client时才有关联。

Answer 1

创建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)
);

然后将其用作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)
);

是否真的删除多余的FK只是个人喜好的问题。