From 89f908a6d0ac1337c868625008c9598487d184e7 Mon Sep 17 00:00:00 2001 From: Peter Eisentraut Date: Tue, 17 Sep 2024 10:41:07 +0200 Subject: Add temporal FOREIGN KEY contraints Add PERIOD clause to foreign key constraint definitions. This is supported for range and multirange types. Temporal foreign keys check for range containment instead of equality. This feature matches the behavior of the SQL standard temporal foreign keys, but it works on PostgreSQL's native ranges instead of SQL's "periods", which don't exist in PostgreSQL (yet). Reference actions ON {UPDATE,DELETE} {CASCADE,SET NULL,SET DEFAULT} are not supported yet. (previously committed as 34768ee3616, reverted by 8aee330af55; this is essentially unchanged from those) Author: Paul A. Jungwirth Reviewed-by: Peter Eisentraut Reviewed-by: jian he Discussion: https://www.postgresql.org/message-id/flat/CA+renyUApHgSZF9-nd-a0+OPGharLQLO=mDHcY4_qQ0+noCUVg@mail.gmail.com --- doc/src/sgml/catalogs.sgml | 3 ++- doc/src/sgml/ref/create_table.sgml | 45 ++++++++++++++++++++++++++++++++++---- 2 files changed, 43 insertions(+), 5 deletions(-) (limited to 'doc/src') diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml index 1e56edaa70b..bfb97865e18 100644 --- a/doc/src/sgml/catalogs.sgml +++ b/doc/src/sgml/catalogs.sgml @@ -2736,7 +2736,8 @@ SCRAM-SHA-256$<iteration count>:&l This constraint is defined with WITHOUT OVERLAPS - (for primary keys and unique constraints). + (for primary keys and unique constraints) or PERIOD + (for foreign keys). diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 9243810c3fe..9d552241a16 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -80,7 +80,7 @@ CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT EXI UNIQUE [ NULLS [ NOT ] DISTINCT ] ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | PRIMARY KEY ( column_name [, ... ] [, column_name WITHOUT OVERLAPS ] ) index_parameters | EXCLUDE [ USING index_method ] ( exclude_element WITH operator [, ... ] ) index_parameters [ WHERE ( predicate ) ] | - FOREIGN KEY ( column_name [, ... ] ) REFERENCES reftable [ ( refcolumn [, ... ] ) ] + FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD column_name ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -1147,8 +1147,8 @@ WITH ( MODULUS numeric_literal, REM REFERENCES reftable [ ( refcolumn ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] (column constraint) - FOREIGN KEY ( column_name [, ... ] ) - REFERENCES reftable [ ( refcolumn [, ... ] ) ] + FOREIGN KEY ( column_name [, ... ] [, PERIOD column_name ] ) + REFERENCES reftable [ ( refcolumn [, ... ] [, PERIOD column_name ] ) ] [ MATCH matchtype ] [ ON DELETE referential_action ] [ ON UPDATE referential_action ] @@ -1164,7 +1164,32 @@ WITH ( MODULUS numeric_literal, REM primary key of the reftable is used. Otherwise, the refcolumn list must refer to the columns of a non-deferrable unique or primary key - constraint or be the columns of a non-partial unique index. The user + constraint or be the columns of a non-partial unique index. + + + + If the last column is marked with PERIOD, it is + treated in a special way. While the non-PERIOD + columns are compared for equality (and there must be at least one of + them), the PERIOD column is not. Instead, the + constraint is considered satisfied if the referenced table has matching + records (based on the non-PERIOD parts of the key) + whose combined PERIOD values completely cover the + referencing record's. In other words, the reference must have a + referent for its entire duration. This column must be a range or + multirange type. In addition, the referenced table must have a primary + key or unique constraint declared with WITHOUT + OVERLAPS. Finally, if the foreign key has a PERIOD + column_name specification + the corresponding refcolumn, + if present, must also be marked PERIOD. If the + refcolumn clause is omitted, + and thus the reftable's primary key constraint chosen, the primary key + must have its final column marked WITHOUT OVERLAPS. + + + + The user must have REFERENCES permission on the referenced table (either the whole table, or the specific referenced columns). The addition of a foreign key constraint requires a @@ -1238,6 +1263,10 @@ WITH ( MODULUS numeric_literal, REM values of the referencing column(s) to the new values of the referenced columns, respectively. + + + In a temporal foreign key, this option is not supported. + @@ -1249,6 +1278,10 @@ WITH ( MODULUS numeric_literal, REM referencing columns, to null. A subset of columns can only be specified for ON DELETE actions. + + + In a temporal foreign key, this option is not supported. + @@ -1262,6 +1295,10 @@ WITH ( MODULUS numeric_literal, REM (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) + + + In a temporal foreign key, this option is not supported. + -- cgit v1.2.3