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

假设我有这样的表：

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表，但相应的行在不同的时间段可能会有所不同 - 因此它现在应该返回不同的行，上个月的行不同等。

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

我需要确定，对于某个时间戳，表中只有一行foo for foo.barid

你所说的“时间戳”似乎是指某个一段的时间.

An 排除约束 https://www.postgresql.org/docs/current/sql-createtable.html#SQL-CREATETABLE-EXCLUDE on a 范围类型 https://www.postgresql.org/docs/current/rangetypes.html，结合等式barid（利用附加模块btree_gist https://www.postgresql.org/docs/current/btree-gist.html）将是完美的解决方案。

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

这需要 Postgres9.2或稍后。

Related:

• 在 PostgreSQL 中使用 EXCLUDE 防止相邻/重叠条目 https://stackoverflow.com/questions/19504727/preventing-adjacent-overlapping-entries-with-exclude-in-postgresql/19505869#19505869

手册有匹配的代码示例！ https://www.postgresql.org/docs/current/rangetypes.html#RANGETYPES-CONSTRAINT