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

我在指定不允许为尚未空出的房间插入新的预订的约束时遇到了困难。例如，在“A”退房之前，客人“B”入住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

文档确实说：

CHECK约束的表达式可能不包含子查询。

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

有一个特殊的机制来访问触发器内的新行。

where子句和触发器操作都可以使用表单“row列名”和"OLD.column-name“的引用访问正在插入、删除或更新的行的元素，其中列名是与触发器关联的表中列的名称。

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;