From 5e43321e3969e8f39ff5ca84b00da119791ac5e9 Mon Sep 17 00:00:00 2001 From: "Paul A. Jungwirth" Date: Tue, 17 Jun 2025 23:23:28 -0700 Subject: [PATCH v58 02/10] Document temporal foreign keys Author: Paul A. Jungwirth --- doc/src/sgml/ddl.sgml | 98 +++++++++++++++++++++ doc/src/sgml/images/Makefile | 3 +- doc/src/sgml/images/temporal-references.svg | 37 ++++++++ doc/src/sgml/images/temporal-references.txt | 21 +++++ 4 files changed, 158 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/ddl.sgml b/doc/src/sgml/ddl.sgml index 74b55005ffe..53d849bf34c 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1758,6 +1758,103 @@ ALTER TABLE products that they also forbid an empty application time. + + + 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 (at least). 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. + + + + We can create the variants table with the following + schema (without a foreign key yet to enforce referential integrity): + + +CREATE TABLE variants ( + id integer NOT NULL, + product_id integer NOT NULL, + name text NOT NULL, + valid_at daterange NOT NULL, + CONSTRAINT variants_pkey + PRIMARY KEY (id, valid_at WITHOUT OVERLAPS), +); + + + We have included a temporal primary key as a best practice, but it is not + strictly required by foreign keys. + + + + plots product 5 (in + green) and two variants referencing it (in yellow) on the same + timeline. 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. + + +
+ Temporal Foreign Key Example + + + + + +
+ + + + In a table, these records would be: + + id | product_id | name | valid_at +----+------------+--------+------------------------- + 8 | 5 | Medium | [2021-01-01,2023-06-01) + 9 | 5 | XXL | [2022-03-01,2024-06-01) + + + + + 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 add a temporal foreign key to our table is: + + +ALTER TABLE variants + ADD CONSTRAINT variants_id_valid_at_fkey + FOREIGN KEY (product_id, PERIOD valid_at) + REFERENCES products (id, PERIOD valid_at); + + + Note that the keyword PERIOD must be used for the + 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. + + @@ -1781,6 +1878,7 @@ ALTER TABLE products Temporal wiki page for possibilities. + 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..15f40413a64 --- /dev/null +++ b/doc/src/sgml/images/temporal-references.svg @@ -0,0 +1,37 @@ + + + + + + + + + + + + + + + + + + + + + + products + (5, 5.00, [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.00, [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..f49040e8846 --- /dev/null +++ b/doc/src/sgml/images/temporal-references.txt @@ -0,0 +1,21 @@ + + ++------------------------------------+------------------------------------------------------+ +| cGRE | cGRE | +| products | products | +| (5, 5.00, [1 Jan 2020,1 Jan 2022)) | (5, 8.00, [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 ... -- 2.45.0