A constraint with function that contains Select is never satisfied

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?

Answer

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.