title: SQL Server Conditional Foreign Key Constraints
body: <p>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:</p>
  
  <pre><code>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)
  </code></pre>
  
  <p>Each Enquiry has exactly one Contact. The link between the two is the UniversalJoin table where </p>
  
  <pre><code>Enquiry.Join_Ref = UniversalJoin.Join_Ref AND 
  Rel_Type = 1 AND
  UniversalJoin.Contact_Ref = Contact.Contact_Ref
  </code></pre>
  
  <p>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.</p>
  
  <p>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:</p>
  
  <pre><code>CREATE TABLE Enquiry 
    ...
    CONSTRAINT FK_Foo 
    FOREIGN KEY (Join_Ref)
    REFERENCES UniversalJoin (JoinRef WHERE Rel_Type=1)
  </code></pre>