日期范围重叠检查约束 [英] Date range overlapping check constraint

问题描述

我在sql server 2005中有一个简单的表，有3列：DateStart，DateEnd和Value。我试图设置一个表检查约束，以避免插入重叠记录。例如，如果在此表中存在DateStart = 2012-01-01（January 1月）和DateEnd 2012-01-15（1月15日）的记录，则Check约束必须避免插入具有DateStart = 2012-01-10（ no care DateEnd），具有DateEnd = 2012-01-10（no care DateStart）的记录或具有DateStart 2011-12-10和DateEnd 2012-02-01的记录。

我以这种方式定义了UDF：

  CREATE FUNCTION [dbo]。[ufn_checkOverlappingDateRange] 
 b $ b @DateStart AS DATETIME 
，@ DateEnd AS DATETIME 
）
 RETURNS BIT 
 AS 
 BEGIN 
 DECLARE @retval BIT 
 / *日期范围至少一天* / 
 IF（DATEDIFF（day，@ DateStart，@ DateEnd）< 1）
 BEGIN 
 SET @ retval = 0 
 END 
 ELSE 
 BEGIN 
如果存在
（
 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。我在哪里wrog？是否可以设置这样的约束？

我认为DateStart包含在范围内，而DateEnd从范围中排除。

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

您需要修改您的WHERE，以包含以下内容： @MyTableId<>

$ b

$ b

范围不要重叠，如果：

• 是在另一个范围的开头之前的另一个


• 或开始之前。

这可以用SQL写成：

  WHERE @DateEnd< DateStart OR DateEnd< @DateStart 
  

否定，以获取 do 重叠的范围...

  WHERE NOT（@DateEnd< DateStart OR DateEnd< @DateStart）
  

...根据 De Morgan的法律与...相同。

  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]

注意：要允许范围触摸，请在起始表达式中使用< = ，这将在最终表达式中生成'>' em>

I've a simple table in sql server 2005 with 3 columns: DateStart, DateEnd and Value. I tried to set a "table check constraint" to avoid inserting overlapping records. For instance if in such table there is a record with DateStart = 2012-01-01 (first January) and DateEnd 2012-01-15 (15th January) than Check constraint must avoid inserting a record with DateStart=2012-01-10 (no care DateEnd), a record with DateEnd=2012-01-10 (no care DateStart) or a record with DateStart 2011-12-10 and DateEnd 2012-02-01.

I defined a UDF in such way:

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

Then thought check could be this:

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

But even with [myTable] empty EXISTS Operator returns true when i insert first record. Where i'm wrog ? Is it possible to set a constraint like this ?

BTW I consider DateStart includes in range and DateEnd excludes from range.

解决方案

The CHECK is being executed after the row has been inserted, so the range overlaps with itself.

You'll need to amend your WHERE to include something like: @MyTableId <> MyTableId.

---

BTW, your WHERE expression can be simplified.

Ranges don't overlap if:

• end of the one range is before the start of the other
• or start of the one range is after the end of the other.

Which could be written in SQL like:

WHERE @DateEnd < DateStart OR DateEnd < @DateStart

Negate that to get the ranges that do overlap...

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

...which according to De Morgan's laws is the same as...

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

...which is the same as:

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

So your final WHERE should be:

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

[SQL Fiddle]

NOTE: to allow ranges to "touch", use <= in the starting expression, which would produce '>' in the final expression.

这篇关于日期范围重叠检查约束的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！