sqlite - 在 CHECK 约束中使用子查询的替代方法？

Question

我正在尝试构建一个简单的酒店房间登记数据库作为学习练习。

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)
);

我无法指定不允许为尚未腾出的房间插入新预订的约束。例如(下图)，客人 'B' 在 'A' 退房之前入住 123 房间。

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');

这不应该被允许，但我不确定如何编写此约束。我的第一次尝试是编写一个子查询检查，但我无法找出正确的子查询，因为我不知道如何访问新插入的 'roomNum' 值来执行子查询。然后我还发现大多数 SQL 系统甚至不允许在 check 内部进行子查询。

那么我应该如何写这个约束呢？我读了一些关于触发器的文章，它们似乎可以解决这个问题，但这真的是唯一的方法吗？或者我只是密集而缺少编写约束的明显方法？

Answer 1

documentation确实 说:

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

虽然可以创建一个返回到数据库并查询表的用户定义函数，但实现此约束的唯一合理方法是使用触发器。

有专门的 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;