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

Commiteda1d0d

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 parent7c53b19 commiteda1d0d

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
@@ -1049,10 +1049,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
10491049
column(s) of some row of the referenced table. If the <replaceable
10501050
class="parameter">refcolumn</replaceable> list is omitted, the
10511051
primary key of the <replaceable class="parameter">reftable</replaceable>
1052-
is used. The referenced columns must be the columns of a non-deferrable
1053-
unique or primary key constraint in the referenced table. The user
1054-
must have <literal>REFERENCES</literal> permission on the referenced table
1055-
(either the whole table, or the specific referenced columns). The
1052+
is used. Otherwise, the <replaceable class="parameter">refcolumn</replaceable>
1053+
list must refer to the columns of a non-deferrable unique or primary key
1054+
constraint or be the columns of a non-partial unique index. The user
1055+
must have <literal>REFERENCES</literal> permission on the referenced
1056+
table (either the whole table, or the specific referenced columns). The
10561057
addition of a foreign key constraint requires a
10571058
<literal>SHARE ROW EXCLUSIVE</literal> lock on the referenced table.
10581059
Note that foreign key constraints cannot be defined between temporary
@@ -2177,6 +2178,16 @@ CREATE TABLE cities_partdef
21772178
</para>
21782179
</refsect2>
21792180

2181+
<refsect2>
2182+
<title>Foreign Key Constraints</title>
2183+
2184+
<para>
2185+
It is a <productname>PostgreSQL</productname> extension that a
2186+
foreign key constraint may reference columns of a unique index instead of
2187+
columns of a primary key or unique constraint.
2188+
</para>
2189+
</refsect2>
2190+
21802191
<refsect2>
21812192
<title><literal>NULL</literal> <quote>Constraint</quote></title>
21822193

‎src/backend/commands/tablecmds.c

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -10560,15 +10560,19 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
1056010560
/*
1056110561
* transformFkeyCheckAttrs -
1056210562
*
10563-
*Make sure that the attributes of a referenced table belong to a unique
10564-
*(or primary key) constraint. Return the OID of the index supporting
10565-
*the constraint, as well as the opclasses associated with the index
10566-
*columns.
10563+
*Validate that the 'attnums' columns in the 'pkrel' relation are valid to
10564+
*reference as part of a foreign key constraint.
10565+
*
10566+
*Returns the OID of the unique index supporting the constraint and
10567+
*populates the caller-provided 'opclasses' array with the opclasses
10568+
*associated with the index columns.
10569+
*
10570+
*Raises an ERROR on validation failure.
1056710571
*/
1056810572
static Oid
1056910573
transformFkeyCheckAttrs(Relation pkrel,
1057010574
int numattrs, int16 *attnums,
10571-
Oid *opclasses) /* output parameter */
10575+
Oid *opclasses)
1057210576
{
1057310577
Oidindexoid = InvalidOid;
1057410578
boolfound = false;

0 commit comments

Comments
 (0)

[8]ページ先頭

©2009-2025 Movatter.jp