postgresql - 跨两个表的排除约束

Question

我有两个表，用于存储两个不同应用程序实体的数据。两个实体都有一些公共(public)字段，特别是父实体的外键和日期范围字段。我可以在每个表上创建排除约束，以确保同一父实体的日期范围不会重叠，但我需要将此约束应用于合并的两个表。有没有一种方法可以在数据库级别实现这一点？

我想我可以创建一个仅包含 parent_id 和 daterange 列的附加表，以及排除约束，并在插入/更新/删除行时在上述两个表上添加触发器，但我对更简单的解决方案感兴趣如果有的话!

Answer 1

如果您希望您的设计保持现状，那么带触发器的解决方案可能是唯一的解决方案。

如果您愿意重新设计，您可以像这样对数据建模:

-- for the exclusion constraint
CREATE EXTENSION btree_gist;

CREATE TABLE parent (
   pid bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY
);

CREATE TABLE ranges (
   rid bigint NOT NULL GENERATED ALWAYS AS IDENTITY,
   pid bigint NOT NULL REFERENCES parent (pid),
   arange daterange NOT NULL,
   PRIMARY KEY (pid, rid),
   EXCLUDE USING gist (pid WITH =, arange WITH &&)
);

CREATE TABLE child1 (
   c1id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   pid bigint NOT NULL REFERENCES parent (pid),
   rid bigint NOT NULL,
   FOREIGN KEY (rid, pid) REFERENCES ranges (rid, pid)
);

CREATE TABLE child2 (
   c2id bigint PRIMARY KEY GENERATED ALWAYS AS IDENTITY,
   pid bigint NOT NULL REFERENCES parent (pid),
   rid bigint NOT NULL,
   FOREIGN KEY (rid, pid) REFERENCES ranges (rid, pid)
);

也就是说，将所有范围保存在一个表中，以便您可以定义排除约束。