Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commitdff1756

Browse files
committed
Doc: mention foreign keys can reference unique indexes
We seem to have only documented a foreign key can reference the columns ofa primary key or unique constraint. Here we adjust the documentationto mention columns in a non-partial unique index can be mentioned too.The header comment for transformFkeyCheckAttrs() also didn't mentionunique indexes, so fix that too. In passing make that header commentreflect reality in the various other aspects where it deviated from it.Bug: 18295Reported-by: Gilles PARCAuthor: Laurenz Albe, David RowleyDiscussion:https://www.postgresql.org/message-id/18295-0ed0fac5c9f7b17b%40postgresql.orgBackpatch-through: 12
1 parentb2fd1da commitdff1756

File tree

3 files changed

+34
-19
lines changed

3 files changed

+34
-19
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1083,16 +1083,16 @@ CREATE TABLE order_items (
10831083

10841084
<para>
10851085
A foreign key must reference columns that either are a primary key or
1086-
form a unique constraint. This means that the referenced columns always
1087-
have an index (theone underlying the primary key or unique constraint);
1088-
so checks on whether a referencing row has a match will be efficient.
1089-
Since a<command>DELETE</command> of a row from the referenced table
1090-
or an<command>UPDATE</command> of a referenced column will require
1091-
a scan ofthe referencing table for rows matching the old value, it
1092-
is often a goodidea to index the referencing columns too. Because this
1093-
is not alwaysneeded, and there are many choices available on how
1094-
to index,declaration of a foreign key constraint does not
1095-
automatically create anindex on the referencing columns.
1086+
form a unique constraint, or are columns from a non-partial unique index.
1087+
This means thatthereferenced columns always have an index to allow
1088+
efficient lookups on whether a referencing row has a match. Since a
1089+
<command>DELETE</command> of a row from the referenced table or an
1090+
<command>UPDATE</command> of a referenced column will require a scan of
1091+
the referencing table for rows matching the old value, it is often a good
1092+
idea to index the referencing columns too. Because this is not always
1093+
needed, and there are many choices available on how to index, the
1094+
declaration of a foreign key constraint does not automatically create an
1095+
index on the referencing columns.
10961096
</para>
10971097

10981098
<para>

‎doc/src/sgml/ref/create_table.sgml

Lines changed: 15 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -1038,10 +1038,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
10381038
column(s) of some row of the referenced table. If the <replaceable
10391039
class="parameter">refcolumn</replaceable> list is omitted, the
10401040
primary key of the <replaceable class="parameter">reftable</replaceable>
1041-
is used. The referenced columns must be the columns of a non-deferrable
1042-
unique or primary key constraint in the referenced table. The user
1043-
must have <literal>REFERENCES</literal> permission on the referenced table
1044-
(either the whole table, or the specific referenced columns). The
1041+
is used. Otherwise, the <replaceable class="parameter">refcolumn</replaceable>
1042+
list must refer to the columns of a non-deferrable unique or primary key
1043+
constraint or be the columns of a non-partial unique index. The user
1044+
must have <literal>REFERENCES</literal> permission on the referenced
1045+
table (either the whole table, or the specific referenced columns). The
10451046
addition of a foreign key constraint requires a
10461047
<literal>SHARE ROW EXCLUSIVE</literal> lock on the referenced table.
10471048
Note that foreign key constraints cannot be defined between temporary
@@ -2137,6 +2138,16 @@ CREATE TABLE cities_partdef
21372138
</para>
21382139
</refsect2>
21392140

2141+
<refsect2>
2142+
<title>Foreign Key Constraints</title>
2143+
2144+
<para>
2145+
It is a <productname>PostgreSQL</productname> extension that a
2146+
foreign key constraint may reference columns of a unique index instead of
2147+
columns of a primary key or unique constraint.
2148+
</para>
2149+
</refsect2>
2150+
21402151
<refsect2>
21412152
<title><literal>NULL</literal> <quote>Constraint</quote></title>
21422153

‎src/backend/commands/tablecmds.c

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -10107,15 +10107,19 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
1010710107
/*
1010810108
* transformFkeyCheckAttrs -
1010910109
*
10110-
*Make sure that the attributes of a referenced table belong to a unique
10111-
*(or primary key) constraint. Return the OID of the index supporting
10112-
*the constraint, as well as the opclasses associated with the index
10113-
*columns.
10110+
*Validate that the 'attnums' columns in the 'pkrel' relation are valid to
10111+
*reference as part of a foreign key constraint.
10112+
*
10113+
*Returns the OID of the unique index supporting the constraint and
10114+
*populates the caller-provided 'opclasses' array with the opclasses
10115+
*associated with the index columns.
10116+
*
10117+
*Raises an ERROR on validation failure.
1011410118
*/
1011510119
static Oid
1011610120
transformFkeyCheckAttrs(Relation pkrel,
1011710121
int numattrs, int16 *attnums,
10118-
Oid *opclasses) /* output parameter */
10122+
Oid *opclasses)
1011910123
{
1012010124
Oidindexoid = InvalidOid;
1012110125
boolfound = false;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp