Conditional foreign key in SQL

Question

I have one table called PartyChannel with the following columns

ID, ChannelID, ChannelType 

ChannelID stores MailID or PhoneID or EmailID depending on ChannelType .

since I can create a foreign key between PartyChannel and all three tables (Mail, Email and Phone) depending on the type of channelType.

Answer 1

You can use the PERSISTED COMPUTED columns with the case argument, but in the end it buys you nothing but the service ones.

A better solution would be to model them as three different values ​​to begin with.

 CREATE TABLE Mails (MailID INTEGER PRIMARY KEY) CREATE TABLE Phones (PhoneID INTEGER PRIMARY KEY) CREATE TABLE Emails (EmailID INTEGER PRIMARY KEY) CREATE TABLE PartyChannel ( ID INTEGER NOT NULL , ChannelID INTEGER NOT NULL , ChannelType CHAR(1) NOT NULL , MailID AS (CASE WHEN [ChannelType] = 'M' THEN [ChannelID] ELSE NULL END) PERSISTED REFERENCES Mails (MailID) , PhoneID AS (CASE WHEN [ChannelType] = 'P' THEN [ChannelID] ELSE NULL END) PERSISTED REFERENCES Phones (PhoneID) , EmailID AS (CASE WHEN [ChannelType] = 'E' THEN [ChannelID] ELSE NULL END) PERSISTED REFERENCES Emails (EmailID) ) 

Renouncement

just because you can doesn’t mean what you need.

Answer 2

AFAIK, you cannot do this with standard foreign keys. However, you can implement something that will help ensure data integrity with triggers. In fact, the trigger will check for the presence of a “foreign key” in the reference table — the value that should be present whenever there is an insert or update in the link table. Similarly, deletion from a referenced table may have a trigger that checks entries in the referenced table that use the deleted key.

Update: although I went right for the “answer”, I agree with the comment posted by @oneday when this is actually a design issue that should probably make you rethink your design. That is, you should have three different columns, not one column referencing three tables. You simply leave the other two columns null when they are full, which in turn allows you to use standard foreign keys. Any care that this is “too much space” is stupid; it is a serious case of premature optimization - it just doesn't matter.

Answer 3

Subtype Email, Mail, Phone to Channel .