SQL Server标量值函数编写：GroupBy后返回布尔值实现检查约束

现有关联表结构 #

CREATE TABLE [dbo].[Cluster] 
(
    [Id]       SMALLINT NOT NULL,
    [IntentId] TINYINT  NOT NULL,
    [ParentId] SMALLINT NULL,

    CONSTRAINT [PK_Cluster] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[ClusterMember] 
(
    [Id]        SMALLINT NOT NULL,
    [ClusterId] SMALLINT NOT NULL,
    [PageId]    INT      NOT NULL,

    CONSTRAINT [PK_ClusterMember] 
        PRIMARY KEY CLUSTERED ([Id] ASC),
    CONSTRAINT [FK_ClusterMember_Page] 
        FOREIGN KEY ([PageId]) REFERENCES [dbo].[PageNode] ([Id]),
    CONSTRAINT [FK_ClusterMember_Cluster] 
        FOREIGN KEY ([ClusterId]) REFERENCES [dbo].[Cluster] ([Id])
);

CREATE TABLE [dbo].[ClusterKeyword] 
(
    [MemberId]  SMALLINT NOT NULL,
    [KeywordId] INT      NOT NULL,

    CONSTRAINT [PK_ClusterKeyword] 
        PRIMARY KEY CLUSTERED ([MemberId] ASC, [KeywordId] ASC),
    CONSTRAINT [FK_ClusterKeyword_ClusterMember] 
        FOREIGN KEY ([MemberId]) REFERENCES [dbo].[ClusterMember] ([Id]),
    CONSTRAINT [FK_ClusterKeyword_Keyword] 
        FOREIGN KEY ([KeywordId]) REFERENCES [dbo].[Keyword] ([Id])
);

约束规则要求 #

• 允许场景：同一KeywordId关联到不同IntentId的Cluster

  keyword1 => member1 => cluster1 (intent 1)
  keyword1 => member2 => cluster2 (intent 2)
  

• 禁止场景：同一KeywordId关联到同一IntentId下的多个Cluster

  keyword1 => member1 => cluster1 (intent 1)
  keyword1 => member2 => cluster2 (intent 1)
  

原函数的问题 #

原函数dbo.ClusterKeywordMismatches存在以下问题：

1. 返回类型为int，不符合检查约束需要的布尔判断需求
2. 逻辑缺失：未关联当前要插入记录的MemberId，无法定位对应的IntentId，仅统计全局分组无法准确判断是否违反规则
3. 连接方式冗余：使用LEFT JOIN会引入无效空值，应该用INNER JOIN确保关联关系有效

修改后的标量函数 #

以下函数返回bit类型，1表示允许添加条目，0表示违反约束禁止添加：

CREATE FUNCTION dbo.IsClusterKeywordAllowed(@KeywordId INT, @MemberId SMALLINT)
RETURNS BIT
AS
BEGIN
    DECLARE @IsAllowed BIT = 1;
    DECLARE @TargetIntentId TINYINT;

    -- 获取当前要添加的Member对应的IntentId
    SELECT @TargetIntentId = c.IntentId
    FROM ClusterMember cm
    INNER JOIN Cluster c ON cm.ClusterId = c.Id
    WHERE cm.Id = @MemberId;

    -- 检查该IntentId下是否已存在相同的KeywordId
    IF EXISTS (
        SELECT 1
        FROM ClusterKeyword ck
        INNER JOIN ClusterMember cm ON ck.MemberId = cm.Id
        INNER JOIN Cluster c ON cm.ClusterId = c.Id
        WHERE ck.KeywordId = @KeywordId
          AND c.IntentId = @TargetIntentId
    )
    BEGIN
        SET @IsAllowed = 0;
    END

    RETURN @IsAllowed;
END

创建检查约束 #

将上述函数绑定到ClusterKeyword表的检查约束：

ALTER TABLE [dbo].[ClusterKeyword]
ADD CONSTRAINT CK_ClusterKeyword_UniquePerIntent
CHECK (dbo.IsClusterKeywordAllowed(KeywordId, MemberId) = 1);

函数逻辑说明 #

1. 通过要插入记录的MemberId，关联查询到对应的IntentId
2. 检查该IntentId下是否已经存在使用相同KeywordId的记录
3. 若存在重复则返回0（禁止添加），否则返回1（允许添加）

内容的提问来源于stack exchange，提问作者John Ohara