[ACCEPTED]-Date range overlapping check constraint-check-constraints
Accepted answer
The CHECK is being executed after the row has 9 been inserted, so the range overlaps with 8 itself.
You'll need to amend your WHERE to 7 include something like: @MyTableId <> MyTableId.
BTW, your WHERE 6 expression can be simplified.
Ranges don't overlap 5 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 4 that to get the ranges that do overlap...
WHERE NOT (@DateEnd < DateStart OR DateEnd < @DateStart)
...which 3 according to De Morgan's laws is the same as...
WHERE NOT (@DateEnd < DateStart) AND NOT (DateEnd < @DateStart)
...which 2 is the same as:
WHERE @DateEnd >= DateStart AND DateEnd >= @DateStart
So your final WHERE should 1 be:
WHERE
@MyTableId <> MyTableId
AND @DateEnd >= DateStart
AND DateEnd >= @DateStart
NOTE: to allow ranges to "touch", use <= in the starting expression, which would produce > in the final expression.
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".
0
Source:
stackoverflow.com
More Related questions
Cookie Warning
We use cookies to improve the performance of the site. By staying on our site, you agree to the terms of use of cookies.