表级约束，以防止日期范围重叠 [英] Table level constraint to prevent overlapping date ranges

问题描述

下面是我需要施加约束的模式，以便即使在相同房间号的现有depDt之前，也可以为房间号添加第二个新条目。你们中的任何一个都可以帮我吗？

Below is the schema where I need to put a constraint such that a second new entry could be put in for a room number, even before the existing depDt for the same room number. Could any of you please help me with this??

CREATE TABLE Accomodation (
  roomNo INTEGER NOT NULL,
  arrDt DATE NOT NULL,
  depDt DATE NOT NULL,
  PRIMARY KEY (roomNo, arrDt), 
  CONSTRAINT date_chk CHECK (arrDt < depDt)
);

INSERT INTO HotelStays(roomNo, arrDt, depDt) VALUES 
  (123, to_date('20160202', 'YYYYMMDD'),to_date('20160206','YYYYMMDD')),
  (123, to_date('20160205', 'YYYYMMDD'), to_date('20160208','YYYYMMDD'));

我尝试在CONSTRAINTS中的WHERE下给出一个子查询，但在SQL Fiddle中不起作用。 / p>

I have tried giving a sub query under WHERE in CONSTRAINTS but its is not working in SQL Fiddle.

推荐答案

可以使用排除约束在日期范围内：

This can be done using an exclusion constraint on the date range:

alter table Accomodation
  add constraint no_overlap 
  exclude using gist (roomno with =, daterange(arrdt, depdt) with &&);

请注意，您需要 btree_gist扩展以在GiST索引中支持 = 运算符。

Note that you need the btree_gist extension to support the = operator in a GiST index.

这篇关于表级约束，以防止日期范围重叠的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！