v59-0002-Document-temporal-foreign-keys.patch
text/x-patch
Filename: v59-0002-Document-temporal-foreign-keys.patch
Type: text/x-patch
Part: 5
From 6f1bf56d9a71bb3a51979d79a3cf3338462f717d Mon Sep 17 00:00:00 2001
From: "Paul A. Jungwirth" <pj@illuminatedcomputing.com>
Date: Tue, 17 Jun 2025 23:23:28 -0700
Subject: [PATCH v59 02/11] Document temporal foreign keys
Author: Paul A. Jungwirth <pj@illuminatedcomputing.com>
---
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.
</para>
</sect3>
+
+ <sect3 id="ddl-application-time-foreign-keys">
+ <title>Temporal Foreign Keys</title>
+
+ <para>
+ 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 <literal>products</literal> table is
+ referenced by a <literal>variants</literal> table, and a variant of
+ product 5 has an application-time of
+ <literal>[2020-01-01,2026-01-01)</literal>, then product 5 must exist
+ throughout that period.
+ </para>
+
+ <para>
+ We can create the <literal>variants</literal> table with the following
+ schema (without a foreign key yet to enforce referential integrity):
+
+<programlisting>
+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),
+);
+</programlisting>
+
+ We have included a temporal primary key as a best practice, but it is not
+ strictly required by foreign keys.
+ </para>
+
+ <para>
+ <xref linkend="temporal-references-figure"/> 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.
+ </para>
+
+ <figure id="temporal-references-figure">
+ <title>Temporal Foreign Key Example</title>
+ <mediaobject>
+ <imageobject>
+ <imagedata fileref="images/temporal-references.svg" format="SVG" width="100%"/>
+ </imageobject>
+ </mediaobject>
+ </figure>
+
+ <para>
+
+ In a table, these records would be:
+<programlisting>
+ id | product_id | name | valid_at
+----+------------+--------+-------------------------
+ 8 | 5 | Medium | [2021-01-01,2023-06-01)
+ 9 | 5 | XXL | [2022-03-01,2024-06-01)
+</programlisting>
+ </para>
+
+ <para>
+ 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.
+ </para>
+
+ <para>
+ The syntax to add a temporal foreign key to our table is:
+
+<programlisting>
+ALTER TABLE variants
+ ADD CONSTRAINT variants_id_valid_at_fkey
+ FOREIGN KEY (product_id, PERIOD valid_at)
+ REFERENCES products (id, PERIOD valid_at);
+</programlisting>
+
+ Note that the keyword <literal>PERIOD</literal> must be used for the
+ application-time column in both the referencing and referenced table.
+ </para>
+
+ <para>
+ A temporal primary key or unique constraint matching the referenced columns
+ must exist on the referenced table.
+ </para>
+
+ <para>
+ <productname>PostgreSQL</productname> supports <literal>NO ACTION</literal>
+ temporal foreign keys, but not <literal>RESTRICT</literal>,
+ <literal>CASCADE</literal>, <literal>SET NULL</literal>, or
+ <literal>SET DEFAULT</literal>.
+ </para>
+ </sect3>
</sect2>
<sect2 id="ddl-system-time">
@@ -1781,6 +1878,7 @@ ALTER TABLE products
Temporal wiki page</ulink> for possibilities.
</para>
</sect2>
+
</sect1>
<sect1 id="ddl-alter">
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 @@
+<?xml version="1.0"?>
+<svg xmlns="http://www.w3.org/2000/svg" viewBox="0 0 1020 350" width="1020" height="350" shape-rendering="geometricPrecision" version="1.0">
+ <defs>
+ <filter id="f2" x="0" y="0" width="200%" height="200%">
+ <feOffset result="offOut" in="SourceGraphic" dx="5" dy="5"/>
+ <feGaussianBlur result="blurOut" in="offOut" stdDeviation="3"/>
+ <feBlend in="SourceGraphic" in2="blurOut" mode="normal"/>
+ </filter>
+ </defs>
+ <g stroke-width="1" stroke-linecap="square" stroke-linejoin="round">
+ <rect x="0" y="0" width="1020" height="350" style="fill: #ffffff"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M395.0 63.0 L945.0 63.0 L945.0 133.0 L395.0 133.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#ffff33" d="M215.0 133.0 L215.0 203.0 L685.0 203.0 L685.0 133.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#ffff33" d="M875.0 203.0 L875.0 273.0 L455.0 273.0 L455.0 203.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="#99dd99" d="M395.0 63.0 L395.0 133.0 L25.0 133.0 L25.0 63.0 z"/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M215.0 294.0 L215.0 307.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M25.0 294.0 L25.0 307.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M405.0 294.0 L405.0 307.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M595.0 294.0 L595.0 307.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M785.0 294.0 L785.0 307.0 "/>
+ <path stroke="#000000" stroke-width="1.000000" stroke-linecap="round" stroke-linejoin="round" fill="none" d="M975.0 294.0 L975.0 307.0 "/>
+ <text x="40" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="40" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, 5.00, [1 Jan 2020,1 Jan 2022))</text>
+ <text x="210" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">2021</text>
+ <text x="230" y="166" font-family="Courier" font-size="15" stroke="none" fill="#000000">variants</text>
+ <text x="230" y="180" font-family="Courier" font-size="15" stroke="none" fill="#000000">(8, 5, 'Medium', [1 Jan 2021,1 Jun 2023))</text>
+ <text x="20" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">2020</text>
+ <text x="470" y="236" font-family="Courier" font-size="15" stroke="none" fill="#000000">variants</text>
+ <text x="470" y="250" font-family="Courier" font-size="15" stroke="none" fill="#000000">(9, 5, 'XXL', [1 Mar 2022,1 Jun 2024))</text>
+ <text x="410" y="96" font-family="Courier" font-size="15" stroke="none" fill="#000000">products</text>
+ <text x="410" y="110" font-family="Courier" font-size="15" stroke="none" fill="#000000">(5, 8.00, [1 Jan 2022,))</text>
+ <text x="590" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">2023</text>
+ <text x="400" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">2022</text>
+ <text x="780" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">2024</text>
+ <text x="979" y="320" font-family="Courier" font-size="15" stroke="none" fill="#000000">...</text>
+ </g>
+</svg>
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.39.5