***实践我发现是创建一个函数，返回传入的值是否存在于您的消息和草稿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