SQL创建表约束 禁止同rationid下日期区间重叠插入

需求说明 #

需通过用户自定义函数为数据表创建校验约束，日期统一采用yyyy-mm-dd格式，目标表包含rationid、startdate、enddate三个字段，合法数据示例如下：

约束规则：

• 同一rationid下，禁止插入日期区间与已有记录重叠的数据
• 新插入记录的startdate必须晚于同rationid下已有记录的enddate，例：同id下第一条记录enddate为2022-02-01时，下一条记录startdate最早只能取2022-02-02，取2022-02-01即属于违规。

原有实现问题 #

原有校验SQL可实现基础重叠拦截，但存在三个问题：

1. 边界判断逻辑漏洞：新插入记录的startdate与已有记录的enddate相等时，语句不会触发拦截，会放行违规数据
2. 字段拼写错误：代码中m.endate为笔误，正确字段名为m.enddate
3. 日期转换样式不匹配：使用样式码101对应mm/dd/yyyy格式，和业务使用的yyyy-mm-dd格式不匹配，存在解析错误风险

原有SQL代码：

SELECT 1
FROM dbo.MAXRation as m
WHERE m.rationid = @rationid
  AND CONVERT(date, @EndDate, 101) > CONVERT(date, m.startdate, 101)
  AND CONVERT(date, m.endate, 101) > CONVERT(date, @StartDate, 101) 
GROUP BY m.rationid
HAVING COUNT(*) > 1

修复实现 #

核心逻辑调整 #

将原双侧开区间的重叠判断逻辑，调整为匹配业务规则的边界判断：只要同rationid下存在任意一条旧记录满足「旧记录enddate >= 新记录startdate」且「新记录enddate >= 旧记录startdate」，即判定为区间冲突，拦截写入。

自定义校验函数 #

CREATE FUNCTION dbo.CheckRationDateOverlap
(
    @rationid INT,
    @StartDate VARCHAR(10),
    @EndDate VARCHAR(10)
)
RETURNS BIT
AS
BEGIN
    -- 用样式码23做yyyy-mm-dd格式转换，避免格式解析错误
    DECLARE @newStart DATE = CONVERT(DATE, @StartDate, 23);
    DECLARE @newEnd DATE = CONVERT(DATE, @EndDate, 23);
    DECLARE @hasConflict BIT = 0;

    -- 用EXISTS替代GROUP BY判断，执行效率更高
    IF EXISTS (
        SELECT 1
        FROM dbo.MAXRation m
        WHERE m.rationid = @rationid
          AND CONVERT(DATE, m.enddate, 23) >= @newStart
          AND @newEnd >= CONVERT(DATE, m.startdate, 23)
    )
    BEGIN
        SET @hasConflict = 1;
    END

    RETURN @hasConflict;
END;

绑定表级CHECK约束 #

将校验函数绑定到目标表，实现插入、更新操作时自动触发校验：

ALTER TABLE dbo.MAXRation
ADD CONSTRAINT CK_MAXRation_DateNoOverlap
CHECK (dbo.CheckRationDateOverlap(rationid, startdate, enddate) = 0);

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