SQL Server条件外键约束 [英] SQL Server Conditional Foreign Key Constraints

问题描述

我在弄清楚如何创建外键约束时遇到了麻烦。我的数据模型是固定的，不受我的控制，它看起来像这样：

I'm having trouble figuring out how to create a foreign key constraint. My data model is fixed and out of my control, it looks like this:

CREATE TABLE Enquiry 
  (Enquiry_Ref INTEGER PRIMARY KEY CLUSTERED, Join_Ref INTEGER, EnquiryDate, EnquiryType...)

CREATE TABLE Contact 
  (Contact_Ref INTEGER PRIMARY KEY CLUSTERED, Surname, Forenames ....)

CREATE TABLE UniversalJoin 
  (Join_Ref INTEGER, Contact_Ref INTEGER, Rel_Type INTEGER)

每次查询仅有一个联系人。两者之间的链接是UniversalJoin表，其中

Each Enquiry has exactly one Contact. The link between the two is the UniversalJoin table where

Enquiry.Join_Ref = UniversalJoin.Join_Ref AND 
Rel_Type = 1 AND
UniversalJoin.Contact_Ref = Contact.Contact_Ref

Rel_Type取决于源表是什么，因此在查询的情况下，Rel_Type为1，但对于另一个表它将设置为N。

The Rel_Type differs depending on what the source table is, so in the case of Enquiry, Rel_Type is 1 but for another table it would set to N.

我的问题是如何创建外键约束来强制执行这种关系的完整性？我想说但不能说的是：

My question is how do I create a foreign key constraint to enforce the integrity of this relationship? What I want to say, but can't, is:

CREATE TABLE Enquiry 
  ...
  CONSTRAINT FK_Foo 
  FOREIGN KEY (Join_Ref)
  REFERENCES UniversalJoin (JoinRef WHERE Rel_Type=1)

推荐答案

在SQL Server中不能使用条件或过滤的外键

You can't use conditional or filtered foreign keys in SQL Server

在这些情况下，您可能会有一个（JoinRef，Rel_Type）之间的多列FK并在UniversalJoin中设置Rel_Type的检查约束以使其变为1。

In these cases, you could have a multiple column FK between (JoinRef, Rel_Type) and set a check constraint on Rel_Type in UniversalJoin to make it 1.

但是，我认为您正在尝试与多个父母做不到。

However, I think you are trying to have a row with multiple parents which can't be done.

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