Foreign key filtering in PostgreSQL

Question

I am currently looking for a way in PostgreSQL where I can, for a given table, limit the number of valid foreign keys available by defining a condition.

So the scenario is like this

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

A, B, C All represent different entities, and each specific entry is listed in the x_entry table.

The tables A, B, C all represents versions of a given entry, an in what time span they are valid.

I want to ensure that the table A always have foreign keys to B and C where the condition A.B.C == A.C for the timespan A.

As seen here

The condition for Ainit for the range 2000-3000 is violated since, binit in the time range 2100-3000 has a foreign key to cinot where ainit changes it foreign key from cinit to cinat violating the condition of A.B.C == A.C

But how do i set up such a conditional limit, that prevent a certain condition like this is not being violated for foreign keys. ? and is it even possible?

user_813125 · Answer

I am currently looking for a way in PostgreSQL where I can, for a given table, limit the number of valid foreign keys available by defining a condition.

You can't do that.

Foreign key constrains enforce referential integrity based on uniqueness, e.g. "table_a.id == table_b.a_id". You can't filter on overlapping date-ranges or anything like that.

I can think of fundamentally one way of doing what you are asking for: Triggers*

It's not too complicated, you add "BEFORE INSERT/DELETE/UPDATE" triggers on each table that rejects all inserts/updates/deletes violating your rules.

If you can restructure your tables it becomes easier, you would just need two tables:

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)

now you only need a insert/update/delete trigger on entry_version that enforces the rules.

Things you might want to look into:

EXCLUDE constraints You could for example make sure you can't put two rows with overlapping date ranges in the same table.

table partitions You could have different partitions for each validity period or something. (is likely a dead end for you though).

Figure out some way of turning the date ranges into rows in a table and reference them with foreign keys.

*yes, there are also RULE:s but that's expert level

user_6317366 · Answer

I ended making a check constrain on both table A and table B Where both checks this function

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
$$;

In table A and table B.

I do see some performance issues with this, since it has to check each row.

Foreign key filtering in PostgreSQL

2 Answers