From 69f2832716dfb6fcf66ca78eb0653a7eb7e17818 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Tue, 17 Jun 2025 23:23:28 -0700 Subject: [PATCH v54 02/11] Document temporal foreign keys Author: Paul A. Jungwirth --- doc/src/sgml/images/Makefile | 3 +- doc/src/sgml/images/temporal-references.svg | 37 ++++++++++++ doc/src/sgml/images/temporal-references.txt | 21 +++++++ doc/src/sgml/temporal.sgml | 67 +++++++++++++++++++++ 4 files changed, 127 insertions(+), 1 deletion(-) create mode 100644 doc/src/sgml/images/temporal-references.svg create mode 100644 doc/src/sgml/images/temporal-references.txt diff --git a/doc/src/sgml/images/Makefile b/doc/src/sgml/images/Makefile index 1d99d4e30c8..fd55b9ad23f 100644 --- a/doc/src/sgml/images/Makefile +++ b/doc/src/sgml/images/Makefile @@ -6,7 +6,8 @@ ALL_IMAGES = \ genetic-algorithm.svg \ gin.svg \ pagelayout.svg \ - temporal-entities.svg + temporal-entities.svg \ + temporal-references.svg DITAA = ditaa DOT = dot diff --git a/doc/src/sgml/images/temporal-references.svg b/doc/src/sgml/images/temporal-references.svg new file mode 100644 index 00000000000..f9091ac9b0a --- /dev/null +++ b/doc/src/sgml/images/temporal-references.svg @@ -0,0 +1,37 @@ + + + + + + + + + + + + + + + + + + + + + + products + (5, $5, [1 Jan 2020,1 Jan 2022)) + 2021 + variants + (8, 5, 'Medium', [1 Jan 2021,1 Jun 2023)) + 2020 + variants + (9, 5, 'XXL', [1 Mar 2022,1 Jun 2024)) + products + (5, $8, [1 Jan 2022,)) + 2023 + 2022 + ... + 2024 + + diff --git a/doc/src/sgml/images/temporal-references.txt b/doc/src/sgml/images/temporal-references.txt new file mode 100644 index 00000000000..01cad1bcf8b --- /dev/null +++ b/doc/src/sgml/images/temporal-references.txt @@ -0,0 +1,21 @@ + + ++-----------------------------------+-----------------------------------------------------+ +| cGRE | cGRE | +| products | products | +| (5, $5, [1 Jan 2020,1 Jan 2022)) | (5, $8, [1 Jan 2022,)) | +| | | ++-----------------+-----------------+--------------------------+--------------------------+ + | cYEL | + | variants | + | (8, 5, 'Medium', [1 Jan 2021,1 Jun 2023)) | + | | + +---------------------+----------------------+------------------+ + | cYEL | + | variants | + | (9, 5, 'XXL', [1 Mar 2022,1 Jun 2024)) | + | | + +-----------------------------------------+ + +| | | | | | +2020 2021 2022 2023 2024 ... diff --git a/doc/src/sgml/temporal.sgml b/doc/src/sgml/temporal.sgml index 5d1f697fee8..4c4febb94b4 100644 --- a/doc/src/sgml/temporal.sgml +++ b/doc/src/sgml/temporal.sgml @@ -162,6 +162,73 @@ command. + + Temporal Foreign Keys + + + A temporal foreign key is a reference from one application-time + table to another application-time table. Just as a non-temporal + reference requires a referenced key to exist, so a temporal reference + requires a referenced key to exist, but during whatever history the + reference exists. So if the products table is + referenced by a variants table, and a variant of + product 5 has an application-time of + [2020-01-01,2026-01-01), then product 5 must exist + throughout that period. + + +
+ Temporal Foreign Key Example + + + + + +
+ + + plots product 5 (in + green) and two variants referencing it (in yellow) on the same + timeline. Each variant tuple is shown with its id, a product id, a + name, and an application-time. So variant 8 (Medium) was introduced + first, then variant 9 (XXL). Both satisfy the foreign key constraint, + because the referenced product exists throughout their entire history. + + + + Note that a temporal reference need not be fulfilled by a single + row in the referenced table. Product 5 had a price change in the middle + of variant 8's history, but the reference is still valid. The + combination of all matching rows is used to test whether the referenced + history contains the referencing row. + + + + The syntax to declare a temporal foreign key is: + + +ALTER TABLE variants + ADD CONSTRAINT variants_id_valid_at_fkey + FOREIGN KEY (id, PERIOD valid_at) + REFERENCES products (id, PERIOD valid_at); + + + Note that the keyword PERIOD must be used for application-time column + in both the referencing and referenced table. + + + + A temporal primary key or unique constraint matching the referenced columns + must exist on the referenced table. + + + + PostgreSQL supports NO ACTION temporal foreign keys, + but not RESTRICT, CASCADE, SET NULL, + or SET DEFAULT. + + + Temporal Update and Delete -- 2.45.0