时间范围的“一对多”关系完整性问题 [英] '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屋！