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
开发者_如何学C (Contact_Ref INTEGER PRIMARY KEY CLUSTERED, Surname, Forenames ....)
CREATE TABLE UniversalJoin
(Join_Ref INTEGER, Contact_Ref INTEGER, Rel_Type INTEGER)
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
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)
You can't use conditional or filtered foreign keys in SQL Server
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.
You might rather want to have a look at CHECK Constraints
CHECK constraints enforce domain integrity by limiting the values that are accepted by a column. They are similar to FOREIGN KEY constraints in that they control the values that are put in a column. The difference is in how they determine which values are valid: FOREIGN KEY constraints obtain the list of valid values from another table, and CHECK constraints determine the valid values from a logical expression that is not based on data in another column.
You could use a table trigger with INSERT and Update to layer the equivalent as a FK.
This way you are able to apply conditions i.e. if column value =1 check exists in table a if column value = 2 then check another table.
精彩评论