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

Commit973e140

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 parent29e25a6 commit973e140

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
@@ -1098,10 +1098,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
10981098
column(s) of some row of the referenced table. If the <replaceable
10991099
class="parameter">refcolumn</replaceable> list is omitted, the
11001100
primary key of the <replaceable class="parameter">reftable</replaceable>
1101-
is used. The referenced columns must be the columns of a non-deferrable
1102-
unique or primary key constraint in the referenced table. The user
1103-
must have <literal>REFERENCES</literal> permission on the referenced table
1104-
(either the whole table, or the specific referenced columns). The
1101+
is used. Otherwise, the <replaceable class="parameter">refcolumn</replaceable>
1102+
list must refer to the columns of a non-deferrable unique or primary key
1103+
constraint or be the columns of a non-partial unique index. The user
1104+
must have <literal>REFERENCES</literal> permission on the referenced
1105+
table (either the whole table, or the specific referenced columns). The
11051106
addition of a foreign key constraint requires a
11061107
<literal>SHARE ROW EXCLUSIVE</literal> lock on the referenced table.
11071108
Note that foreign key constraints cannot be defined between temporary
@@ -2233,6 +2234,16 @@ CREATE TABLE cities_partdef
22332234
</para>
22342235
</refsect2>
22352236

2237+
<refsect2>
2238+
<title>Foreign Key Constraints</title>
2239+
2240+
<para>
2241+
It is a <productname>PostgreSQL</productname> extension that a
2242+
foreign key constraint may reference columns of a unique index instead of
2243+
columns of a primary key or unique constraint.
2244+
</para>
2245+
</refsect2>
2246+
22362247
<refsect2>
22372248
<title><literal>NULL</literal> <quote>Constraint</quote></title>
22382249

‎src/backend/commands/tablecmds.c

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -10953,15 +10953,19 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
1095310953
/*
1095410954
* transformFkeyCheckAttrs -
1095510955
*
10956-
*Make sure that the attributes of a referenced table belong to a unique
10957-
*(or primary key) constraint. Return the OID of the index supporting
10958-
*the constraint, as well as the opclasses associated with the index
10959-
*columns.
10956+
*Validate that the 'attnums' columns in the 'pkrel' relation are valid to
10957+
*reference as part of a foreign key constraint.
10958+
*
10959+
*Returns the OID of the unique index supporting the constraint and
10960+
*populates the caller-provided 'opclasses' array with the opclasses
10961+
*associated with the index columns.
10962+
*
10963+
*Raises an ERROR on validation failure.
1096010964
*/
1096110965
static Oid
1096210966
transformFkeyCheckAttrs(Relation pkrel,
1096310967
int numattrs, int16 *attnums,
10964-
Oid *opclasses) /* output parameter */
10968+
Oid *opclasses)
1096510969
{
1096610970
Oidindexoid = InvalidOid;
1096710971
boolfound = false;

0 commit comments

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