日期范围重叠检查约束

Question

日期范围重叠检查约束

sql-serverdatabasesql-server-2005database-designcheck-constraints

29

我在SQL Server 2005中有一个简单的表，包含3列：DateStart、DateEnd和Value。我试图设置一个"表检查约束"来避免插入重叠的记录。例如，如果在这样的表中有一条记录，其中DateStart = 2012-01-01（1月1日），而DateEnd = 2012-01-15（1月15日），则检查约束必须防止插入具有DateStart=2012-01-10（不关心DateEnd）的记录，具有DateEnd=2012-01-10（不关心DateStart）的记录或具有DateStart=2011-12-10和DateEnd=2012-02-01的记录。

我定义了一个UDF：

CREATE FUNCTION [dbo].[ufn_checkOverlappingDateRange]
(
    @DateStart AS DATETIME
    ,@DateEnd AS DATETIME
)
RETURNS BIT 
AS
BEGIN
  DECLARE @retval BIT
  /* date range at least one day */
  IF (DATEDIFF(day,@DateStart,@DateEnd) < 1)
    BEGIN
      SET @retval=0
    END
  ELSE
    BEGIN
      IF EXISTS
        (
          SELECT
              *
            FROM [dbo].[myTable]
            WHERE
            ((DateStart <= @DateStart) AND (DateEnd > @DateStart))
            OR
            ((@DateStart <= DateStart) AND (@DateEnd > DateStart))
        )
        BEGIN
          SET @retval=0
        END
    ELSE
      BEGIN
            SET @retval=1
          END
        END
  RETURN @retval
END

那么这个想法的检查可能是这样的：

ALTER TABLE [dbo].[myTable]  WITH CHECK ADD  CONSTRAINT [CK_OverlappingDateRange] CHECK  ([dbo].[ufn_checkOverlappingDateRange]([DateStart],[DateEnd])<>(0))

即使在 [myTable] 为空的情况下，当我插入第一条记录时 EXISTS 操作符仍然返回 true。我错了吗？是否可能设置这样的约束？

顺便说一下，我认为 DateStart 包括在范围内，而 DateEnd 则不包括在范围内。

- Antonio Manello

3个回答

1


CREATE TABLE [dbo].[TEMPLATE] (
    [ID]             BIGINT       IDENTITY (1, 1) NOT NULL,
    [DATE_START]     DATETIME     NOT NULL,
    [DATE_END]       DATETIME     NOT NULL,
    [TEMPLATE_NAME]  VARCHAR (50) NOT NULL,
    CONSTRAINT [PK_TEMPLATE] PRIMARY KEY CLUSTERED ([ID] ASC),
    CONSTRAINT [FK_current_start_and_end_dates_in_sequence] CHECK ([DATE_START]<=[DATE_END])
);

go


CREATE FUNCTION [dbo].[Check_Period]
(
    @start DateTime,
    @end DateTime
)
RETURNS INT
AS
BEGIN
    declare @result INT = 0 ;
    set @result = (Select count(*) from [dbo].[TEMPLATE] F where F.DATE_START <= @start and F.DATE_END >= @start );

    set @result = @result +  
    (
         Select count(*) from [dbo].[TEMPLATE] F where F.DATE_START <= @end and F.DATE_END >= @end 
    ) 
    RETURN @result
END

go

ALTER TABLE [dbo].[TEMPLATE]
    ADD CONSTRAINT [FK_overlap_period_t]
    CHECK ([dbo].[Check_Period]([DATE_START],[DATE_END])=(2));

go

Insert Into [dbo].[TEMPLATE] (DATE_START, DATE_END, TEMPLATE_NAME) values ('2020-01-01','2020-12-31', 'Test1');
-- (1 row(s) affected)
Insert Into [dbo].[TEMPLATE] (DATE_START, DATE_END, TEMPLATE_NAME) values ('2021-01-01','2022-12-31', 'Test2');
-- (1 row(s) affected)
Insert Into [dbo].[TEMPLATE] (DATE_START, DATE_END, TEMPLATE_NAME) values ('2020-01-01','2020-12-31', 'Test3');
-- The INSERT statement conflicted with the CHECK constraint "FK_overlap_period_t".

- user1785960

0

我想在Branko Dimitrijevic的答案上补充一点，即当DateEnd为空时的情况，因为我目前遇到了这种情况。

这可能发生在你记录打卡日志并且用户仍然登录的情况下。

WHERE
    @MyTableId <> MyTableId
    AND @DateEnd >= DateStart
    AND DateEnd >= @DateStart
    OR @DateEnd >= DateStart
    AND DateEnd is null
    OR @DateStart >= DateStart
    AND DateEnd is null

我不知道这个查询的性能如何，我相信有优化的方法。

- effy

3

没有括号，这个代码不会产生你想象中的效果。 - Yllzarith

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Branko Dimitrijevic · Accepted Answer

在插入行之后执行CHECK，因此范围与自身重叠。

您需要修改WHERE子句以包括类似@MyTableId <> MyTableId的内容。

顺便说一下，您的WHERE表达式可以简化。

如果:

一个范围的结束在另一个范围的开始之前
或者一个范围的开始在另一个范围的结束之后，则范围不会重叠。

这可以用SQL编写如下：

WHERE @DateEnd < DateStart OR DateEnd < @DateStart

取反以获取那些确实重叠的范围...

WHERE NOT (@DateEnd < DateStart OR DateEnd < @DateStart)

根据德摩根定律，这与...相同。

WHERE NOT (@DateEnd < DateStart) AND NOT (DateEnd < @DateStart)

...这与以下代码等价：

WHERE @DateEnd >= DateStart AND DateEnd >= @DateStart

因此，您的最终 WHERE 子句应为：

WHERE
    @MyTableId <> MyTableId
    AND @DateEnd >= DateStart
    AND DateEnd >= @DateStart

[SQL Fiddle]

注意：为了让范围“接触”，在起始表达式中使用<=，这将产生最终表达式中的>。