A constraint with function that contains Select is never satisfied

Question

I have a table like this:

CREATE TABLE CarRentSchedule
(
    carId INTEGER NOT NULL,
    rent_start INTEGER NOT NULL,
    rent_end INTEGER NOT NULL,
    personId INTEGER NOT NULL,
    tariff INTEGER NOT NULL,
    PRIMARY KEY carId,
    FOREIGN KEY (personId) REFERENCES Persons(PersonID)
    CONSTRAINT CHK_CR_AVLBL CHECK (dbo.CarCheck(carId , rent_start , rent_end ) = 1))

And I need to forbid any rent time intersection for the same car. I found that, when a function is used (example below) inside the constraint, the value it returns is different from whent it is used outside the constraint.

if exists (select * from dbo.CarRentSchedule rt
               where rt.carId = @id 
                 and ((rt.rent_Start >= @startTime and rt.rent_end < @endTime)
                      or  
                      (rt.rent_end <= @endTime and rt.rent_end > @startTime)
                      or 
                      (rt.rent_Start < @endTime and rt.rent_end > @startTime)
                      or 
                      (rt.rent_Start >= @startTime and rt.rent_end <= @endTime)))
        return 0

So when I'm trying to insert data into the table, the constraint would never allow me to for some reason.

I have checked, when called from the constraint the function seems to return NULL, however calling the function like

select dbo.CheckFunc(1, 10, 15, 1, 1)

would return 0/1 as it is supposed to. I have also checked and found out that if there is no SELECT in the function (a simple comparison operation, for example) it would work just fine in the constraint too.

Could anybody explain why does this work this way? Is it some restriction forbidding using SELECT's inside the constraints or is it because the SELECT references to the same table the constraint applied to?

I think you have to write your function to avoid selecting *the row you're trying to check*. You're also overcomplicating your overlap logic. Two periods overlap if the first starts before the second ends, and the second starts before the first ends. Just two comparisons to cover all "cases" of overlaps. — Damien_The_Unbeliever, Feb 17 '20 at 11:37
Thank you! Yes, you are absolutely right this one particular example is not efficient at all. But this is merely an example of the problem. Im trying to understang how presence of select in the function makes it act wrong in the constraint, because outside of the constraint it returns expected values. I tryed some other examamples, just so happend that end up with that one when decided to ask stackoverflow community. — daaibraanies, Feb 17 '20 at 19:58
My guess is that when checking the second constraint the new enty is already considered to be a part of the table so it always overlaps with itself. But this is seems strainge, doesn't it? — daaibraanies, Feb 17 '20 at 20:27

score 0 · Answer 1 · edited Feb 18 '20 at 00:12

Can you try below if that works :

Alter function AvailableCar
    (@car integer, @startTime integer, @endTime integer)
returns integer 
as
begin
declare @value int
    set @value = (select max(1) from dbo.CarRentSchedule as rt
               where rt.roomId = @car 
                 and ((rt.rent_Start >= @startTime and p.occ_start < @endTime)
                      or  
                      (rt.rent_end <= @endTime and rt.rent_end > @startTime)
                      or 
                      (rt.rent_Start < @endTime and rt.rent_end > @startTime)
                      or 
                      (rt.rent_Start >= @startTime and rt.rent_end <= @endTime)))
       if (@value is NULL)
       set @value = 0

       return @value


end

Gordon Linoff · Answer 2 · 2020-02-18T20:15:04.667

0

This answers the original version of the question.

Your first condition should be handled with a check constraint:

check (occ_end > occ_start)

Then, correct logic for overlaps might help. I would suggest:

if exists (select 1
           from dbo.RoomSchedule rs
           where p.roomId = @rn and
                 p.occ_end >= @startTime and 
                 p.occ_start < @endTime
          )

Two time periods overlap if the first starts before the second ends and the first ends after the first starts.

edited Feb 18 '20 at 20:15

answered Feb 17 '20 at 14:21

Gordon Linoff

1,242,037
58
646
786

Please be aware that the content of the question was [substantially changed](https://stackoverflow.com/posts/60256938/revisions) after your answer was posted. – Callum Watkins Feb 18 '20 at 18:09

daaibraanies · Accepted Answer · 2020-02-17T22:52:58.613

My guess is that when checking the second constraint the new enty is already considered to be a part of the table so it always overlaps with itself. But this is seems strainge, doesn't it?

So, yes, it appears as so, I have rewirritten the function so that it would not consider the newly added entry and it works now. Strange, tho, I just wouldn't imagine that an entry that is currently being tested to be added would participate in the side select from that table.

A constraint with function that contains Select is never satisfied

3 Answers3