1

I would like to do two things in PostgresSQL:

  • version rows in a table by a date range
  • ensure the integrity of the table by setting up single column foreign keys

For me, it seems I can do only one of the above at the same time.

Detailed example:

I need to version a content of a table based on the date range (so in any particular point in time there is only one row for the (customId, validFrom, validUntil) unique index (there are no overlapping ranges). but it's important that none of those columns are unique by themselves.

By using this method I can query my table and get the valid entity for any point in time, but I could not figure out how to link this table via the customId key to another table so the integrity of the table is guarded.

The problem is that the customId key is not unique as there can be more than one of the same key when multiple ranges are recorded.

One solution I have used before is creating an another x_history table when I am only interested in the latest state of the entity, and copy the old state to the history table every time, but this time, this wouldn't work really well because I would constantly query two table as it's "random" what version of data I am interested in during selects.

Example by data:

table a:

  • id (PK)
  • custom_id (unique in any single point of time via the above composite unique index)
  • valid_from (timestamp, storing the start of the validity of a)
  • valid_until (timestamp, storing the end of the validity of a)

table b:

  • id (PK)
  • a__custom_id (unique in any single point of time)
  • valid_from (timestamp, storing the start of the validity of b)
  • valid_until (timestamp, storing the end of the validity of b)

I would like to insert only those rows into table b which

  • b.a__custom_id exists in a.custom_id
  • b.a_custom_id, b.valid_from, b.valid_until is unique
NoNameProvided
  • 8,608
  • 9
  • 40
  • 68

1 Answers1

0

You cannot easily have both foreign keys and historical data.

One way would be to have the validity range as part of the primary key, but then you have to update many rows whenever you modify an entry in the referenced table.

I think you can get away with a history table if you include the currently active version in the history table. Then you can just query the history table, and the table with the current values is just there for foreign keys.

The history table would have an exclusion constraint over the primary key and the time range.

Laurenz Albe
  • 209,280
  • 17
  • 206
  • 263