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.

MailID, PhoneID, EmailID are three distinct domains and cannot, should not be modelled using a single ChannelID column. This is fundamental stuff. — onedaywhen, Dec 23 '10 at 14:35

Lieven Keersmaekers · Answer 1 · 2010-12-23T15:21:09.870

18

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.

edited Dec 23 '10 at 15:21

answered Dec 23 '10 at 14:42

Lieven Keersmaekers

57,207
13
112
146

This is pretty wild. Does it work in all the major SQL implementations? – Mark Brittingham Dec 23 '10 at 14:45
@Mark: it works with SQL Server 2005 & 2008. Other than that, I honestly have no idea, it was a good mental excercise . A disclaimer might be in order though: *just because you can doesn't mean you should!* – Lieven Keersmaekers Dec 23 '10 at 14:51
It does work in SQL Server although your ChannelType should be a char or varchar rather than an int. Again - +1 - thanks. Pretty interesting and unusual stuff. I still agree with @onedaywhen that this isn't a good design but I appreciate the lesson in offbeat SQL that you have provided! – Mark Brittingham Dec 23 '10 at 14:57
Oops - just saw your comment. RE: "just because you can..." - you said it right. It truly never ceases to amaze me just how much you can do with SQL. – Mark Brittingham Dec 23 '10 at 14:58
@Mark, thanks for spotting that. I have fixed the ChannelType type. – Lieven Keersmaekers Dec 23 '10 at 15:21
@LievenKeersmaekers, You suggest to not use this. What's the down side? – FLICKER Jan 23 '21 at 00:56
@FLICKER - the comment from onedaywhen explains that better than I could: *MailID, PhoneID, EmailID are three distinct domains and cannot, should not be modelled using a single ChannelID column.* – Lieven Keersmaekers Jan 23 '21 at 09:51

score 8 · Answer 2 · answered Dec 23 '10 at 18:54

8

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

alt text

answered Dec 23 '10 at 18:54

Damir Sudarevic

21,891
3
47
71

This is the gen-spec pattern, as represented in a relational model. The diagram illustrates the keys nicely. Good job. – Walter Mitty Dec 24 '10 at 10:11

Mark Brittingham · Answer 3 · 2010-12-23T14:43:07.927

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.

Conditional foreign key in SQL

3 Answers3

Linked