diff options
Diffstat (limited to 'doc/src')
| -rw-r--r-- | doc/src/sgml/catalogs.sgml | 3 | ||||
| -rw-r--r-- | doc/src/sgml/ref/create_table.sgml | 45 |
2 files changed, 43 insertions, 5 deletions
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$<replaceable><iteration count></replaceable>:<replaceable>&l </para> <para> This constraint is defined with <literal>WITHOUT OVERLAPS</literal> - (for primary keys and unique constraints). + (for primary keys and unique constraints) or <literal>PERIOD</literal> + (for foreign keys). </para></entry> </row> 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 ] ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> | PRIMARY KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, <replaceable class="parameter">column_name</replaceable> WITHOUT OVERLAPS ] ) <replaceable class="parameter">index_parameters</replaceable> | EXCLUDE [ USING <replaceable class="parameter">index_method</replaceable> ] ( <replaceable class="parameter">exclude_element</replaceable> WITH <replaceable class="parameter">operator</replaceable> [, ... ] ) <replaceable class="parameter">index_parameters</replaceable> [ WHERE ( <replaceable class="parameter">predicate</replaceable> ) ] | - FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ] + FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] ) REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ] } [ DEFERRABLE | NOT DEFERRABLE ] [ INITIALLY DEFERRED | INITIALLY IMMEDIATE ] @@ -1147,8 +1147,8 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM <varlistentry id="sql-createtable-parms-references"> <term><literal>REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ]</literal> (column constraint)</term> - <term><literal>FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] ) - REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] ) ] + <term><literal>FOREIGN KEY ( <replaceable class="parameter">column_name</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] ) + REFERENCES <replaceable class="parameter">reftable</replaceable> [ ( <replaceable class="parameter">refcolumn</replaceable> [, ... ] [, PERIOD <replaceable class="parameter">column_name</replaceable> ] ) ] [ MATCH <replaceable class="parameter">matchtype</replaceable> ] [ ON DELETE <replaceable class="parameter">referential_action</replaceable> ] [ ON UPDATE <replaceable class="parameter">referential_action</replaceable> ]</literal> @@ -1164,7 +1164,32 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM primary key of the <replaceable class="parameter">reftable</replaceable> is used. Otherwise, the <replaceable class="parameter">refcolumn</replaceable> 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. + </para> + + <para> + If the last column is marked with <literal>PERIOD</literal>, it is + treated in a special way. While the non-<literal>PERIOD</literal> + columns are compared for equality (and there must be at least one of + them), the <literal>PERIOD</literal> column is not. Instead, the + constraint is considered satisfied if the referenced table has matching + records (based on the non-<literal>PERIOD</literal> parts of the key) + whose combined <literal>PERIOD</literal> 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 <literal>WITHOUT + OVERLAPS</literal>. Finally, if the foreign key has a PERIOD + <replaceable class="parameter">column_name</replaceable> specification + the corresponding <replaceable class="parameter">refcolumn</replaceable>, + if present, must also be marked <literal>PERIOD</literal>. If the + <replaceable class="parameter">refcolumn</replaceable> clause is omitted, + and thus the reftable's primary key constraint chosen, the primary key + must have its final column marked <literal>WITHOUT OVERLAPS</literal>. + </para> + + <para> + The user must have <literal>REFERENCES</literal> 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 <replaceable class="parameter">numeric_literal</replaceable>, REM values of the referencing column(s) to the new values of the referenced columns, respectively. </para> + + <para> + In a temporal foreign key, this option is not supported. + </para> </listitem> </varlistentry> @@ -1249,6 +1278,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM referencing columns, to null. A subset of columns can only be specified for <literal>ON DELETE</literal> actions. </para> + + <para> + In a temporal foreign key, this option is not supported. + </para> </listitem> </varlistentry> @@ -1262,6 +1295,10 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM (There must be a row in the referenced table matching the default values, if they are not null, or the operation will fail.) </para> + + <para> + In a temporal foreign key, this option is not supported. + </para> </listitem> </varlistentry> </variablelist> |