Conditional foreign key in SQL

Question

i have one table called as PartyChannel having following columns

 ID, ChannelID, ChannelType

ChannelID stores MailID or PhoneID or EmailID depending on the ChannelType.

so how can i create a foreign key between PartyChannel and all three tables (Mail, Email and Phone) depending on the channelType.

Answer 1

You can use PERSISTED COMPUTED columns with a case statement but in the end, it buys you nothing but overhead.

The best solution would be to model them as three distinct values to start 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)
)

Disclaimer

just because you can doesn't mean you should.