SQL中的条件外键 [英] Conditional foreign key in SQL

问题描述

我有一个称为PartyChannel的表，该表具有以下列

i have one table called as PartyChannel having following columns

 ID, ChannelID, ChannelType

ChannelID 存储 MailID 或 PhoneID 或 EmailID ，具体取决于 ChannelType 。

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

所以我如何根据channelType在PartyChannel和所有三个表（邮件，电子邮件和电话）之间创建外键。

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

推荐答案

您可以使用永久计算带有case语句的列，但最后，它除了开销之外没有其他好处。

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)
)

免责声明

仅仅是因为您不能代表您应该这样做。

这篇关于SQL中的条件外键的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！