postgresql - 可以创建外键以指向 PostgreSQL 中日期范围版本表中的非唯一列吗？

Question

我想在 PostgresSQL 中做两件事：

• 按日期范围对表格中的行进行版本化
• 通过设置单列外键确保表的完整性

对我来说，似乎我只能同时做以上其中一项。

详细示例：

我需要根据日期范围对表格的内容进行版本化（因此在任何特定时间点， ( customId, validFrom, validUntil) 唯一索引只有一行（没有重叠范围）。但重要的是这些都不是列本身是唯一的。

通过使用这种方法，我可以查询我的表并获取任何时间点的有效实体，但我无法弄清楚如何通过customId密钥将此表链接到另一个表，以便保护表的完整性。

问题是customId键不是唯一的，因为当记录多个范围时，可能有多个相同的键。

我以前使用的一种解决方案是在我只对实体的最新状态感兴趣时创建另一个x_history表，并且每次都将旧状态复制到历史表中，但是这一次，这不会很好，因为我会不断查询两个表，因为它是“随机”的，我在选择过程中对什么版本的数据感兴趣。

数据示例：

表一：

• id(PK)
• custom_id（通过上述复合唯一索引在任何单个时间点唯一）
• valid_from（时间戳，存储a的有效性开始）
• valid_until（时间戳，存储a的有效期结束）

表b：

• id(PK)
• a__custom_id（在任何单个时间点都是唯一的）
• valid_from（时间戳，存储b的有效性开始）
• valid_until（时间戳，存储b的有效期结束）

我只想将那些行插入到表b中

• b.a__custom_id存在于a.custom_id
• b.a_custom_id, b.valid_from,b.valid_until是唯一的

标签： postgresqlforeign-keysdatabase-relations

Answer 1

您不能轻易地同时拥有外键和历史数据。

一种方法是将有效范围作为主键的一部分，但是当您修改引用表中的条目时，您必须更新许多行。

我认为如果您在历史记录表中包含当前活动的版本，您可以摆脱历史记录表。然后你可以只查询历史表，具有当前值的表就在那里用于外键。

历史表将对主键和时间范围有一个排除约束。