Postgresql - 范围约束 - 两个表

Question

With constraints on ranges ，我们防止在现有表中添加任何重叠值。例如，在下面的 room_reservation 表中，我们确保在冲突时间没有预订房间。

CREATE EXTENSION btree_gist;
CREATE TABLE room_reservation (
    room text,
    during tsrange,
    EXCLUDE USING GIST (room WITH =, during WITH &&)
);

我们在这里还需要考虑另一个表(房间)也有 room 和 during 字段，并在进行预订时考虑该表中的记录？

我们的具体场景是考试管理。我们有监考表(房间预订)和类(class)表。添加监考记录后，我们需要确保它不与任何其他监考记录重合，并确保当时那个房间没有讲座。

Answer 1

您不能使用单个排除约束来做到这一点。相反，您应该在一个表上使用排除约束，比如 invigilation，然后在同一个表上使用 BEFORE INSERT 触发器来检查第二个表是否存在冲突表，比如 rooms。第一个表上的触发器函数将对第二个表进行简单的范围检查:

CREATE FUNCTION check_no_class() RETURNS trigger AS $$
BEGIN
  PERFORM * FROM rooms
  WHERE room = NEW.room
    AND during && NEW.during;
  IF FOUND THEN
    RETURN NULL;
  ELSE
    RETURN NEW;
  END IF;
END; $$ LANGUAGE plpgsql;

CREATE TRIGGER check_rooms
BEFORE INSERT ON invigilation
FOR EACH ROW EXECUTE PROCEDURE check_no_class();

如果类(class)安排在房间内，则插入invigilation 将失败。