如何在SQL Server中约束ServiceRate表仅存唯一有效日期行

给定SQL Server中的数据表，能否通过约束或唯一索引实现：仅允许用户插入基于开始和结束日期的唯一“有效（Active/Live）”行？

现有数据库架构 #

CREATE TABLE [dbo].[Service]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ServiceName] varchar(50) NOT NULL,

    CONSTRAINT [PK_Service] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ServiceRate]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [ServiceId] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [CountryId] [int] NOT NULL,

    CONSTRAINT [PK_ServiceRate] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[ServiceRate] WITH CHECK 
    ADD CONSTRAINT [FK_ServiceRate_Service] 
        FOREIGN KEY([ServiceId]) REFERENCES [dbo].[Service] ([Id])
GO

ALTER TABLE [dbo].[ServiceRate] CHECK CONSTRAINT [FK_ServiceRate_Service]
GO

ALTER TABLE [dbo].[ServiceRate] WITH CHECK 
    ADD CONSTRAINT [CK_ServiceRate_StartDate_EndDate] 
        CHECK  (([StartDate] <= COALESCE([EndDate], '9999-12-31')))
GO

ALTER TABLE [dbo].[ServiceRate] CHECK CONSTRAINT [CK_ServiceRate_StartDate_EndDate]
GO

CREATE UNIQUE NONCLUSTERED INDEX [UX_ServiceId_EndDate_CountryId] 
ON [dbo].[ServiceRate] ([ServiceId] ASC, [EndDate] ASC, [CountryId] ASC)

需求说明 #

同一(ServiceId, CountryId)组合的日期区间需无重叠，添加重叠行时SQL Server需抛出错误，确保任意日期对应的rec值始终不大于1（如下查询所示）：

DECLARE @dt datetime = '2023-02-14'

SELECT ServiceId, CountryId, COUNT(*) rec
FROM [dbo].[ServiceRate]
WHERE @dt >= StartDate
  AND @dt <  COALESCE(EndDate, '9999-12-31')
GROUP BY ServiceId, CountryId

示例中，以下插入操作应触发错误：

注：CountryId为0代表“所有国家”，仅需保证同一(ServiceId, CountryId)内区间不重叠，不同CountryId之间无需互斥。

解决方案 #

方法1：自定义函数+CHECK约束 #

唯一索引无法直接处理区间重叠场景，通过自定义函数检查插入/更新的行是否与现有行重叠，再通过CHECK约束强制执行规则：

1. 创建重叠检查函数

CREATE FUNCTION dbo.CheckServiceRateOverlap
(
    @ServiceId INT,
    @CountryId INT,
    @StartDate DATETIME,
    @EndDate DATETIME,
    @Id INT -- 更新时排除当前行本身
)
RETURNS BIT
AS
BEGIN
    DECLARE @IsOverlap BIT = 0;
    -- 统一处理NULL EndDate，转为查询用的最大日期
    DECLARE @NewEndDate DATETIME = COALESCE(@EndDate, '9999-12-31');

    -- 检查是否存在重叠的区间：新区间的开始早于现有区间的结束，且新区间的结束晚于现有区间的开始
    IF EXISTS (
        SELECT 1
        FROM dbo.ServiceRate
        WHERE Id <> @Id
          AND ServiceId = @ServiceId
          AND CountryId = @CountryId
          AND @StartDate < COALESCE(EndDate, '9999-12-31')
          AND @NewEndDate > StartDate
    )
    BEGIN
        SET @IsOverlap = 1;
    END

    RETURN @IsOverlap;
END
GO

2. 添加CHECK约束

ALTER TABLE dbo.ServiceRate
ADD CONSTRAINT CK_ServiceRate_NoOverlap
CHECK (dbo.CheckServiceRateOverlap(ServiceId, CountryId, StartDate, EndDate, Id) = 0);
GO

方法2：优化性能（可选） #

为提升函数查询效率，给ServiceRate表创建覆盖索引：

CREATE NONCLUSTERED INDEX IX_ServiceRate_ServiceId_CountryId_Dates
ON dbo.ServiceRate (ServiceId, CountryId, StartDate, EndDate)
INCLUDE (Id); -- 包含Id用于排除自身
GO

注意事项 #

• 该方案同时支持插入和更新操作：更新行的日期时，函数会排除当前行，避免误判自身重叠。
• 若表数据量极大，CHECK约束会带来一定性能开销，此时可考虑将逻辑移至存储过程，在插入/更新前主动检查并抛出错误。
• 原有的UX_ServiceId_EndDate_CountryId唯一索引无法满足需求，可根据需要删除。

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