Conditional Foreign Key In Sql

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

Solution 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.

CREATETABLE Mails (MailID INTEGERPRIMARY KEY)
CREATETABLE Phones (PhoneID INTEGERPRIMARY KEY)
CREATETABLE Emails (EmailID INTEGERPRIMARY KEY)

CREATETABLE PartyChannel (
  ID INTEGERNOTNULL
  , ChannelID INTEGERNOTNULL
  , ChannelType CHAR(1) NOTNULL
  , MailID AS (CASEWHEN [ChannelType] ='M'THEN [ChannelID] ELSENULLEND) PERSISTED REFERENCES Mails (MailID)
  , PhoneID AS  (CASEWHEN [ChannelType] ='P'THEN [ChannelID] ELSENULLEND) PERSISTED REFERENCES Phones (PhoneID)
  , EmailID AS  (CASEWHEN [ChannelType] ='E'THEN [ChannelID] ELSENULLEND) PERSISTED REFERENCES Emails (EmailID)
)

Disclaimer

just because you can doesn't mean you should.

Solution 2:

Sub-type Email, Mail, Phone to the Channel.

Solution 3:

AFAIK, you cannot do this with standard foreign keys. However, you could implement something to help ensure data integrity by using triggers. Essentially, the trigger would check for the presence of a "foreign key" on the referenced table - the value that must be present - whenever there is an insert or update on the referencing table. Similarly, a delete from the referenced table could have a trigger that checks for records on the referencing table that use the key being deleted.

Update: Although I went right for the "answer" I agree with the comment left by @onedaywhen that this is actually a design-caused problem that should probably make you reconsider your design. That is, you should have three different columns rather than one column referencing three tables. You would just leave the other two columns null when one is filled which, in turn, would let you use standard foreign keys. Any concern that this would "use too much space" is silly; it represents a severe case of premature optimization - it just isn't going to matter.