Prevent meeting overlap using UDF in Check() constraint

Question

Prevent meeting overlap using UDF in Check() constraint

j.b.shirk@gmail.com

SSC-Addicted

Points: 443
More actions
September 27, 2007 at 8:22 am

#178964

I followed this misguided strategy as posted in this thread
http://www.sqlservercentral.com/Forums/Topic402679-338-1.aspx
because I could not solve the problem below. Now I realize that solution is very difficult and probably is the wrong approach. So back to the original plan:
I have a table in which meetings are recorded, which should not overlap for any given location:
CREATE TABLE SBSSM.EventSegment
(
EventSegment_ID int IDENTITY (1, 1) NOT NULL,
StartTime datetime NOT NULL,
EndTime datetime NOT NULL,
Activity_ID int NOT NULL,
Location_code nvarchar(20) NOT NULL,
[DBTimeStamp] [timestamp] NULL,
CONSTRAINT IUC220 PRIMARY KEY(EventSegment_ID),
constraint ETGTST check (EndTime > StartTime)
)
GO
here is my UDF, which is supposed to check for existing meetings that conflict with a proposed addition:
CREATE
--alter
FUNCTION sbssm.EventSegmentOvrlap (@loc varchar(20), @s-2 datetime,@e datetime)
RETURNS int
--WITH SCHEMABINDING
AS
BEGIN
RETURN(
select count(*)
from sbssm.EventSegment
where Location_code = @loc and NOT (@s >= endtime or @e <= starttime)
)
END
GO
now I add the constraint to the table:
Alter Table sbssm.EventSegment
ADD constraint EVTSEGMTOVRLAP
Check(sbssm.EventSegmentOvrlap(Location_code,StartTime,EndTime)=0)
now, I have read that a check() constraint is violated iff it evaluates to FALSE
I add a first test record, which violates the check and is not added:
insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code)
Select '2007-09-24 17:00','2007-09-24 18:00',1,'PL'
but when I test the UDF it does what is expected:
print sbssm.EventSegmentOvrlap(
'PL','2007-09-24 17:00','2007-09-24 18:00' --returns 1
)
print sbssm.EventSegmentOvrlap(
'PL','2007-09-24 16:00','2007-09-24 17:00' -- returns 0
)
I reverse the logic of the check:
truncate table sbssm.eventsegment
go
Alter Table sbssm.EventSegment drop constraint EVTSEGMTOVRLAP
go
Alter Table sbssm.EventSegment
ADD constraint EVTSEGMTOVRLAP
Check(sbssm.EventSegmentOvrlap(Location_code,StartTime,EndTime)>0)
Now, I can add the same record again and again, and these duplicates passes the check.
where's the rub?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Your check constraint is being evaluated after the data is put into the table, so the function is returning 1 because you have added the record. Make the function exclude the record you are inserting or have it look for a count > 1

Jeff Moden SSC Guru Points: 1004362 More actions · Answer 2

Heh... I know that's gotta be right... just inserting 1 row into a brand new copy of the table gives me... (note that I run this type of code in TempDB... makes it easier to cleanup)...

Server: Msg 547, Level 16, State 1, Line 1

INSERT statement conflicted with TABLE CHECK constraint 'EVTSEGMTOVRLAP'. The conflict occurred in database 'tempdb', table 'EventSegment'.

The statement has been terminated.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

j.b.shirk@gmail.com SSC-Addicted Points: 443 More actions · Answer 3

Michael Earl (9/27/2007)
Your check constraint is being evaluated after the data is put into the table, so the function is returning 1 because you have added the record. Make the function exclude the record you are inserting or have it look for a count > 1

Brilliant! And thank you very much. I would have never guessed that the record is first added before being checked.

that's odd -- i thought the whole purpose of a constraint is to prevent a violating record from being added. I guess what really happens it's it's added, and if it violates the constraint, it gets rolled back.

In this case, an overlapping time segment for a location should be treated in a similar manner as when a duplicate violates a uniqueness constraint.

so I tried your first suggestion:

Alter Table sbssm.EventSegment

DROP constraint EVTSEGMTOVRLAP

go

--alter

FUNCTION sbssm.EventSegmentOvrlap (@loc varchar(20), @s-2 datetime,@e datetime)

RETURNS int

--WITH SCHEMABINDING

AS

BEGIN

declare @overlap int

set @overlap = 0

if (select count(*)

from sbssm.EventSegment

where Location_code = @loc and NOT (@s >= endtime or @e <= starttime)

)

> 1 set @overlap = 1

RETURN @overlap

END

GO

Alter Table sbssm.EventSegment

ADD constraint EVTSEGMTOVRLAP

Check(sbssm.EventSegmentOvrlap(Location_code,StartTime,EndTime) = 0)

This works great for single insertions. Now, this may not be critical, but I'm curious:

when I insert multiple records, some of which - but not all - should violate, none of them get inserted. iow, it appears the whole transaction is rolled back. any way around this aside from a loop?

--test data, rolls back in total

insert into sbssm.eventsegment (StartTime, EndTime, Activity_ID, Location_code)

Select '2007-09-24 17:00','2007-09-24 18:00',1,'PL' Union ALL

Select '2007-09-24 16:00','2007-09-24 17:00',1,'PL' Union ALL

Select '2007-09-24 17:00','2007-09-24 18:00',1,'PL' Union ALL

Select '2007-09-24 16:30','2007-09-24 17:30',1,'PL' Union ALL

Select '2007-09-24 15:00','2007-09-24 16:00',1,'PL' Union ALL

Select '2007-09-24 15:00','2007-09-24 16:00',1,'PL2' Union ALL

Select '2007-09-24 15:00','2007-09-24 16:00',2,'PL' Union ALL

Select '2007-09-24 15:00','2007-09-24 16:00',2,'PL3' Union ALL

Select '2007-09-24 18:00','2007-09-24 19:00',2,'PL'

--test data, inserts 5 valid records