Alternative to using subquery inside CHECK constraint?

Question

I am trying to build a simple hotel room check-in database as a learning exercise.

CREATE TABLE HotelReservations
(
    roomNum INTEGER NOT NULL,
    arrival DATE NOT NULL,
    departure DATE NOT NULL,
    guestName CHAR(30) NOT NULL,

    CONSTRAINT timeTraveler CHECK (arrival < departure) /* stops time travelers*/
    /* CONSTRAINT multipleReservations CHECK (my question is about this) */

    PRIMARY KEY (roomNum, arrival)
);

I am having trouble specifying a constraint that doesn't allow inserting a new reservation for a room that has not yet been vacated. For example (below), guest 'B' checks into room 123 before 'A' checks out.

INSERT INTO HotelStays(roomNum, arrival, departure, guestName)
VALUES 
    (123, date("2017-02-02"), date("2017-02-06"), 'A'),
    (123, date("2017-02-04"), date("2017-02-08"), 'B');

This shouldn't be allowed but I am unsure how to write this constraint. My first attempt was to write a subquery in check, but I had trouble figuring out the proper subquery because I don't know how to access the 'roomNum' value of a new insert to perform the subquery with. I then also figured out that most SQL systems don't even allow subquerying inside of check.

So how am I supposed to write this constraint? I read some about triggers which seem like it might solve this problem, but is that really the only way to do it? Or am I just dense and missing an obvious way to write the constraint?

Answer 1

The documentation indeed says:

The expression of a CHECK constraint may not contain a subquery.

While it would be possible to create a user-defined function that goes back to the database and queries the table, the only reasonable way to implement this constraint is with a trigger.

There is a special mechanism to access the new row inside the trigger:

Both the WHEN clause and the trigger actions may access elements of the row being inserted, deleted or updated using references of the form "NEW.column-name" and "OLD.column-name", where column-name is the name of a column from the table that the trigger is associated with.

CREATE TRIGGER multiple_reservations_check
BEFORE INSERT ON HotelReservations
BEGIN
    SELECT RAISE(FAIL, "reservations overlap")
    FROM HotelReservations
    WHERE roomNum = NEW.roomNum
      AND departure > NEW.arrival
      AND arrival < NEW.departure;
END;