时间范围的“一对多”关系完整性问题 [英] 'one-to-many' relation integrity issue for time ranges
问题描述
假设我有表格:
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,但关系可以通过时间改变:
The purpose for that table is to provide a pseudo 'one-to-many' relation between tables bar and baz, but the relation can change through time:
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
表中,但对应的行可能在不同的时间段有所不同 - 因此它应该返回不同的行,现在,不同于上个月等。
We can imagine, that for a certain row from bar
table we want to find a corresponding row in baz
table, but the corresponding row may be different in different time periods - so it should return different row for now, different for last month etc.
现在我的问题是:在这个表中验证数据完整性的最好方法是什么?具体来说,我需要确保,对于某个时间戳,表 foo
中只有一行 foo.barid
。我知道我可以写一个触发器(这似乎是我现在的唯一选择),但也许有人有一个更简单的想法?我想在使用某种部分索引,但我不知道如何写一个条件...
Now my question is: what would be the best way to validate data integrity in this table? To be specific, I need to be sure, that for a certain timestamp, there will be only one row in table foo
for foo.barid
. I know I can write a trigger (which seems the only option for my by now), but maybe someone has a simpler idea? I was thinking of using some kind of partial index, but I'm not sure how to write a condition ...
推荐答案
我需要确保,对于某个时间戳,表
foo
中只有一行foo。 barid
并通过 timestamp
排除约束 >范围类型,结合 barid
上的等号(使用附加模块 btree_gist
)将是完美的解决方案。
An exclusion constraint on a range type, combined with equality on barid
(utilizing the additional module btree_gist
) would be the perfect solution.
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版本。这需要Postgres 9.2 或更高版本。
You neglected to provide your version of Postgres. This requires Postgres 9.2 or later.
请考虑这个相关的答案:
在PostgreSQL中避免使用EXCLUDE的相邻/重叠条目
Consider this related answer:
Preventing adjacent/overlapping entries with EXCLUDE in PostgreSQL
这篇关于时间范围的“一对多”关系完整性问题的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!