postgresql - 时间范围的“一对多”关系完整性问题

Question

假设我有这样的表:

CREATE TABLE foo (
  id SERIAL PRIMARY KEY
  , barid integer NOT NULL REFERENCES bar(id) 
  , bazid integer NOT NULL REFERENCES baz(id)
  , startdate timestamp(0) NOT NULL
  , enddate timestamp(0) NOT NULL
);

该表的目的是在表 bar 和 baz 之间提供伪“一对多”关系，但这种关系会随着时间而改变:

SELECT * FROM bar
JOIN foo on TRUE
  AND foo.barid = bar.id
  AND now() BETWEEN foo.startdate  AND foo.enddate 
JOIN baz on baz.id = foo.bazid

我们可以想象，对于bar表中的某一行，我们想在baz表中找到对应的行，但是不同时间对应的行可能不同periods - 所以它现在应该返回不同的行，上个月不同等等。

现在我的问题是:验证此表中数据完整性的最佳方法是什么？具体来说，我需要确定，对于某个时间戳，表 foo 中只有一行用于 foo.barid。我知道我可以写一个触发器(这似乎是我现在唯一的选择)，但也许有人有更简单的想法？我正在考虑使用某种部分索引，但我不确定如何编写条件...

Answer 1

I need to be sure, that for a certain timestamp, there will be only one row in table foo for foo.barid

“时间戳”似乎是指某个时间段。

安exclusion constraint在 range type 上，结合 barid 上的相等性(利用附加模块 btree_gist )将是完美的解决方案。

CREATE EXTENSION btree_gist;  -- needed once per database

CREATE TABLE foo (
  fooid  serial PRIMARY KEY
, barid  integer NOT NULL REFERENCES bar(barid) 
, bazid  integer NOT NULL REFERENCES baz(bazid)
, time_range tsrange NOT NULL           -- replaces startdate  & enddate 
, EXCLUDE USING gist (barid WITH =, time_range WITH &&)
);

这需要 Postgres 9.2 或更高版本。

相关:

• Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL

The manual has a matching code example!