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

Commitc85977d

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 parent97287bd commitc85977d

File tree

3 files changed

+32
-21
lines changed

3 files changed

+32
-21
lines changed

‎doc/src/sgml/ddl.sgml

Lines changed: 10 additions & 10 deletions
Original file line numberDiff line numberDiff line change
@@ -1318,16 +1318,16 @@ CREATE TABLE posts (
13181318

13191319
<para>
13201320
A foreign key must reference columns that either are a primary key or
1321-
form a unique constraint. This means that the referenced columns always
1322-
have an index (theone underlying the primary key or unique constraint);
1323-
so checks on whether a referencing row has a match will be efficient.
1324-
Since a<command>DELETE</command> of a row from the referenced table
1325-
or an<command>UPDATE</command> of a referenced column will require
1326-
a scan ofthe referencing table for rows matching the old value, it
1327-
is often a goodidea to index the referencing columns too. Because this
1328-
is not alwaysneeded, and there are many choices available on how
1329-
to index,declaration of a foreign key constraint does not
1330-
automatically create anindex on the referencing columns.
1321+
form a unique constraint, or are columns from a non-partial unique index.
1322+
This means thatthereferenced columns always have an index to allow
1323+
efficient lookups on whether a referencing row has a match. Since a
1324+
<command>DELETE</command> of a row from the referenced table or an
1325+
<command>UPDATE</command> of a referenced column will require a scan of
1326+
the referencing table for rows matching the old value, it is often a good
1327+
idea to index the referencing columns too. Because this is not always
1328+
needed, and there are many choices available on how to index, the
1329+
declaration of a foreign key constraint does not automatically create an
1330+
index on the referencing columns.
13311331
</para>
13321332

13331333
<para>

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

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1165,10 +1165,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
11651165
column(s) of some row of the referenced table. If the <replaceable
11661166
class="parameter">refcolumn</replaceable> list is omitted, the
11671167
primary key of the <replaceable class="parameter">reftable</replaceable>
1168-
is used. The referenced columns must be the columns of a non-deferrable
1169-
unique or primary key constraint in the referenced table. The user
1170-
must have <literal>REFERENCES</literal> permission on the referenced table
1171-
(either the whole table, or the specific referenced columns). The
1168+
is used. Otherwise, the <replaceable class="parameter">refcolumn</replaceable>
1169+
list must refer to the columns of a non-deferrable unique or primary key
1170+
constraint or be the columns of a non-partial unique index. The user
1171+
must have <literal>REFERENCES</literal> permission on the referenced
1172+
table (either the whole table, or the specific referenced columns). The
11721173
addition of a foreign key constraint requires a
11731174
<literal>SHARE ROW EXCLUSIVE</literal> lock on the referenced table.
11741175
Note that foreign key constraints cannot be defined between temporary
@@ -2305,13 +2306,19 @@ CREATE TABLE cities_partdef
23052306
</refsect2>
23062307

23072308
<refsect2>
2308-
<title>Foreign-KeyConstraint Actions</title>
2309+
<title>ForeignKeyConstraints</title>
23092310

23102311
<para>
2311-
The ability to specify column lists in the foreign-key actions
2312+
The ability to specify column lists in the foreignkey actions
23122313
<literal>SET DEFAULT</literal> and <literal>SET NULL</literal> is a
23132314
<productname>PostgreSQL</productname> extension.
23142315
</para>
2316+
2317+
<para>
2318+
It is a <productname>PostgreSQL</productname> extension that a
2319+
foreign key constraint may reference columns of a unique index instead of
2320+
columns of a primary key or unique constraint.
2321+
</para>
23152322
</refsect2>
23162323

23172324
<refsect2>

‎src/backend/commands/tablecmds.c

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -12117,15 +12117,19 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
1211712117
/*
1211812118
* transformFkeyCheckAttrs -
1211912119
*
12120-
*Make sure that the attributes of a referenced table belong to a unique
12121-
*(or primary key) constraint. Return the OID of the index supporting
12122-
*the constraint, as well as the opclasses associated with the index
12123-
*columns.
12120+
*Validate that the 'attnums' columns in the 'pkrel' relation are valid to
12121+
*reference as part of a foreign key constraint.
12122+
*
12123+
*Returns the OID of the unique index supporting the constraint and
12124+
*populates the caller-provided 'opclasses' array with the opclasses
12125+
*associated with the index columns.
12126+
*
12127+
*Raises an ERROR on validation failure.
1212412128
*/
1212512129
static Oid
1212612130
transformFkeyCheckAttrs(Relation pkrel,
1212712131
int numattrs, int16 *attnums,
12128-
Oid *opclasses) /* output parameter */
12132+
Oid *opclasses)
1212912133
{
1213012134
Oidindexoid = InvalidOid;
1213112135
boolfound = false;

0 commit comments

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