外键列映射到多个主键 [英] Foreign Key column mapped to multiple primary keys

问题描述

我有一个有三个表的数据库

消息 - PK = MessageId

草稿 - PK = DraftId

历史记录 - FK = RelatedItemId

历史记录表有一个外键 [RelatedItemId] 消息和草稿中的两个主键之一。

这个关系有没有名字？

这只是不好的设计吗？

有更好的方式设计这种关系吗？

以下是此问题的CREATE TABLE语句：

  CREATE TABLE [dbo] 。[History]（
 [HistoryId] [uniqueidentifier] NOT NULL，
 [RelatedItemId] [uniqueidentifier] NULL，
 CONSTRAINT [PK_History] ​​PRIMARY KEY CLUSTERED（[HistoryId] ASC）
）
 
 CREATE TABLE [dbo]。[Messages]（
 [MessageId] [uniqueidentifier] NOT NULL，
 CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED（[MessageId] ASC） 
）
 
 
 CREATE TABLE [dbo]。[Drafts]（
 [DraftId] [uniqueidentifier] NOT NULL，
 CONSTRAINT [PK_Drafts] PRIMARY KEY CLUSTERED（[DraftId] ASC）
）
  

解决方案

最佳实践我发现是创建一个函数，返回传入的值是否存在于您的消息和草稿PK列中。然后，您可以在调用此函数的历史记录上的列上添加约束，并且只有在它通过（即它存在）时才会插入。

添加未解析的示例代码：

CREATE FUNCTION is_related_there（
IN @value uniqueidentifier）
RETURNS TINYINT
BEGIN
IF（从草稿中选择计数（DraftId），其中DraftId = @value + select count（MessageId）from Messages where MessageId = @value）> 0 THEN
返回1;
ELSE
RETURN 0;
END IF;
END;

ALTER TABLE历史记录ADD CONSTRAINT
CK_HistoryExists CHECK（is_related_there（RelatedItemId）= 1）

希望运行和帮助lol

I have a database which has three tables

Messages - PK = MessageId
Drafts - PK = DraftId
History - FK = RelatedItemId

The History table has a single foreign Key [RelatedItemId] which maps to one of the two Primary keys in Messages and Drafts.

Is there a name for this relationship?

Is it just bad design?

Is there a better way to design this relationship?

Here are the CREATE TABLE statements for this question:

 CREATE TABLE [dbo].[History](
    [HistoryId] [uniqueidentifier] NOT NULL,
    [RelatedItemId] [uniqueidentifier] NULL,
    CONSTRAINT [PK_History] PRIMARY KEY CLUSTERED ( [HistoryId] ASC )
 )

CREATE TABLE [dbo].[Messages](
    [MessageId] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_Messages] PRIMARY KEY CLUSTERED (    [MessageId] ASC )
 )


CREATE TABLE [dbo].[Drafts](
    [DraftId] [uniqueidentifier] NOT NULL,
    CONSTRAINT [PK_Drafts] PRIMARY KEY CLUSTERED (  [DraftId] ASC )
)

解决方案

Best practice I have found is to create a Function that returns whether the passed in value exists in either of your Messages and Drafts PK columns. You can then add a constraint on the column on the History that calls this function and will only insert if it passes (i.e. it exists).

Adding non-parsed example Code:

CREATE FUNCTION is_related_there ( IN @value uniqueidentifier ) RETURNS TINYINT BEGIN IF (select count(DraftId) from Drafts where DraftId = @value + select count(MessageId) from Messages where MessageId = @value) > 0 THEN RETURN 1; ELSE RETURN 0; END IF; END;

ALTER TABLE History ADD CONSTRAINT CK_HistoryExists CHECK (is_related_there (RelatedItemId) = 1)

Hope that runs and helps lol

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