CODEWITHSUNDEEP
sql-servert-sql
meph
meph

Reputation: 47

Function works in query but not in constraint

I have the following problem: I have a database of a Hotel, and I have to handle collisions while booking rooms.

So let's say, Quest1 wants to book room2 between 2016-11-10 and 2016-11-15, but this room has been booked by Quest3 between 2016-11-07 and 2016-11-14 (You can book the room FROM the day people leave), and my function should be called in a constraint to tell he cant book it for those dates.

I have an EMPTY table for bookings. The problem is, that when using the function in a query, it returns that room2 was booked 0 times between 2016-11-10 and 2016-11-15 (since the table is empty. even if its not empty, it will return the right value too, tested it.), but while using it as a constraint, it comes up as the row was not updated, the insert statement is conflicted with my constraint.

The function:

ALTER FUNCTION [dbo].[Booked]
(
   @room int,
   @from date,
   @to date
)
RETURNS int
AS
BEGIN

return(
   SELECT count(*)
   FROM BOOKINGS
   WHERE room = @room AND NOT (@from >= to OR @to < from)
)

END

Constraint:

ALTER TABLE BOOKINGS ADD 
CONSTRAINT chk_isBooked 
CHECK ([dbo].Booked([room], [from],[to])=(0))

Thanks in advance!

Upvotes: 2

Views: 62

Answers (1)

TT.
TT.

Reputation: 16146

The following script does work. It appears that when the CHECK constraint is fired, the row is already in the table and is being counted in the function. I added an indentity to the bookings table to avoid that.

CREATE TABLE dbo.bookings(
    id int identity(1,1),
    room int ,
    [from] date,
    [to] date
);
GO

CREATE FUNCTION dbo.IsBooked(@id int,@room int, @from date, @to date)
RETURNS BIT
AS 
BEGIN
    RETURN CASE WHEN EXISTS(SELECT * FROM bookings WHERE id<>@id AND room=@room AND NOT(@from>=[to] OR @to<[from])) THEN 1 ELSE 0 END
END
GO

ALTER TABLE dbo.bookings ADD CONSTRAINT chk_isbooked CHECK (dbo.IsBooked(id,room,[from],[to])=0);
GO

INSERT INTO dbo.bookings(room,[from],[to])VALUES(1,'20160101','20160107'); -- success
INSERT INTO dbo.bookings(room,[from],[to])VALUES(1,'20160101','20160107'); -- fail

Upvotes: 1

Related Questions