PostgreSQL 中的外键过滤

Question

PostgreSQL 中的外键过滤

postgresqlforeign-keysconstraints

6

我目前正在寻找一种在PostgreSQL中可以限制给定表的有效外键数量的方法，方法是定义一个条件。

因此，场景如下所示：

https://dbfiddle.uk/?rdbms=postgres_11&fiddle=4dbe279906dc881598b7e72093534ce7

A、B、C分别代表不同的实体，每个具体条目都列在x_entry表中。

表A、B、C都代表给定条目的版本，以及它们有效的时间跨度。

我想确保表A始终具有对B和C的外键，其中条件为A.B.C == A.C适用于A的时间范围。

如下所示

对于时间范围2000-3000的Ainit，违反了条件，因为在时间范围2100-3000内的binit具有到cinot的外键，而ainit将其外键从cinit更改为cinat，违反了A.B.C == A.C的条件。

但是，我该如何设置这样的条件限制，以防止外键违反某些条件？这是否可能呢？

- Carlton Banks

id BIGSERIAL REFERENCES C_entry(id)， - wildplasser

为什么这没有意义？我正在引用另一个表的 ID。 - Carlton Banks

外键指的是另一张表中的主键（或其他唯一键）。它可以是一个大型序列。但外键本身不应该是一个序列。（否则它将与所引用的键相互独立） - wildplasser

@wildplasser 我可能误解了，但postgresql语法规定外键本身也需要指定类型。它不能被未指定。 https://www.postgresql.org/docs/8.3/tutorial-fk.html - J.Down

2

我认为@wildplasser的意思是，类型应该是BIGINT而不是BIGSERIAL。 - Greg

如果你仔细阅读文档：https://www.postgresql.org/docs/8.3/datatype-numeric.html#datatype-serial，BIGERIAL是定义类型和序列的捷径。 - Greg

2个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Björn Nilsson · Answer 1

我目前正在寻找PostgreSQL中的一种方法，可以通过定义条件来限制给定表中可用的有效外键数量。

很抱歉，你不能这样做。外键约束基于唯一性强制执行引用完整性，例如“table_a.id == table_b.a_id”。您无法过滤重叠的日期范围或类似的内容。

我可以想到根本上一种实现您要求的方法：触发器*

这并不太复杂，您只需在每个表上添加“BEFORE INSERT / DELETE / UPDATE”触发器，拒绝所有违反规则的插入/更新/删除操作。

如果您可以重新构造表格，那么它会变得更容易，您只需要两个表格：

entry
  id
  type ("a", "b" ,"c")
  name ("cinit", etc.)

PK entry(id, type)

entry_version
  id
  entry_id
  type ("a", "b" ,"c")
  valid_period (tsrange)

PK entry_version(id)
FK entry_version(entry_id, type) -> entry(id, type)

现在你只需要在entry_version上添加一个插入/更新/删除触发器来强制执行规则。

你可能想要研究一下以下内容： 排除约束 例如，你可以确保不能在同一张表中放置两行具有重叠日期范围的数据。 表分区 你可以为每个有效期设置不同的分区或其他方式。（对你来说可能是个死胡同）。

找出一种将日期范围转换为表中的行，并使用外键引用它们的方法。

*是的，还有规则（RULE），但那是专家级别的。

- Carlton Banks · Answer 2

我在表A和表B上都创建了一个检查约束，双方都检查这个函数。

CREATE OR REPLACE FUNCTION lookup_filtering_check(
    intermediate_table regclass, 
    intermediate_table_column_name text, 
    timespan tsrange,
    lookup_value uuid,
    entity_id uuid,
    OUT result boolean
)
    LANGUAGE plpgsql AS
$$
BEGIN
   EXECUTE 
        FORMAT(
            'SELECT ('
                'NOT EXISTS('
                    'SELECT FROM %1$I '
                    'WHERE %1$I.valid && %3$L '
                    'AND %1$I.id = %5$L '
                    'AND %1$I.%2$I<> %4$L '
                ')'
            ')', 
            intermediate_table, 
            intermediate_table_column_name, 
            timespan, 
            lookup_value,
            entity_id)
   USING intermediate_table, timespan, lookup_value, id
   INTO result;

END
$$;

在表A和表B中。

我发现这个存在一些性能问题，因为它必须检查每一行。