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?

Leonard

You are asking multiple questions and they're all duplicates. Please first google them, with and without stackoverflow, with and without SQLite. Eg What SQL databases support subqueries in CHECK, Sub queries in check constraint, Creating a conditional SQL trigger in SQLite, Date range overlapping check constraint. You need triggers. Re your particular constraint query see my answer re Find date range overlaps within the same table. – Neritic

You cannot do what you want with a check constraint -- unless you use a user defined function. But if you are going to do that, you might as well use a trigger or stored procedure anyway. – Euglena

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;

Lox