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

Commit6fc8a7b

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 parent3726c1c commit6fc8a7b

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
@@ -1154,16 +1154,16 @@ CREATE TABLE posts (
11541154

11551155
<para>
11561156
A foreign key must reference columns that either are a primary key or
1157-
form a unique constraint. This means that the referenced columns always
1158-
have an index (theone underlying the primary key or unique constraint);
1159-
so checks on whether a referencing row has a match will be efficient.
1160-
Since a<command>DELETE</command> of a row from the referenced table
1161-
or an<command>UPDATE</command> of a referenced column will require
1162-
a scan ofthe referencing table for rows matching the old value, it
1163-
is often a goodidea to index the referencing columns too. Because this
1164-
is not alwaysneeded, and there are many choices available on how
1165-
to index,declaration of a foreign key constraint does not
1166-
automatically create anindex on the referencing columns.
1157+
form a unique constraint, or are columns from a non-partial unique index.
1158+
This means thatthereferenced columns always have an index to allow
1159+
efficient lookups on whether a referencing row has a match. Since a
1160+
<command>DELETE</command> of a row from the referenced table or an
1161+
<command>UPDATE</command> of a referenced column will require a scan of
1162+
the referencing table for rows matching the old value, it is often a good
1163+
idea to index the referencing columns too. Because this is not always
1164+
needed, and there are many choices available on how to index, the
1165+
declaration of a foreign key constraint does not automatically create an
1166+
index on the referencing columns.
11671167
</para>
11681168

11691169
<para>

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

Lines changed: 13 additions & 6 deletions
Original file line numberDiff line numberDiff line change
@@ -1108,10 +1108,11 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
11081108
column(s) of some row of the referenced table. If the <replaceable
11091109
class="parameter">refcolumn</replaceable> list is omitted, the
11101110
primary key of the <replaceable class="parameter">reftable</replaceable>
1111-
is used. The referenced columns must be the columns of a non-deferrable
1112-
unique or primary key constraint in the referenced table. The user
1113-
must have <literal>REFERENCES</literal> permission on the referenced table
1114-
(either the whole table, or the specific referenced columns). The
1111+
is used. Otherwise, the <replaceable class="parameter">refcolumn</replaceable>
1112+
list must refer to the columns of a non-deferrable unique or primary key
1113+
constraint or be the columns of a non-partial unique index. The user
1114+
must have <literal>REFERENCES</literal> permission on the referenced
1115+
table (either the whole table, or the specific referenced columns). The
11151116
addition of a foreign key constraint requires a
11161117
<literal>SHARE ROW EXCLUSIVE</literal> lock on the referenced table.
11171118
Note that foreign key constraints cannot be defined between temporary
@@ -2248,13 +2249,19 @@ CREATE TABLE cities_partdef
22482249
</refsect2>
22492250

22502251
<refsect2>
2251-
<title>Foreign-KeyConstraint Actions</title>
2252+
<title>ForeignKeyConstraints</title>
22522253

22532254
<para>
2254-
The ability to specify column lists in the foreign-key actions
2255+
The ability to specify column lists in the foreignkey actions
22552256
<literal>SET DEFAULT</literal> and <literal>SET NULL</literal> is a
22562257
<productname>PostgreSQL</productname> extension.
22572258
</para>
2259+
2260+
<para>
2261+
It is a <productname>PostgreSQL</productname> extension that a
2262+
foreign key constraint may reference columns of a unique index instead of
2263+
columns of a primary key or unique constraint.
2264+
</para>
22582265
</refsect2>
22592266

22602267
<refsect2>

‎src/backend/commands/tablecmds.c‎

Lines changed: 9 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -11414,15 +11414,19 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid,
1141411414
/*
1141511415
* transformFkeyCheckAttrs -
1141611416
*
11417-
*Make sure that the attributes of a referenced table belong to a unique
11418-
*(or primary key) constraint. Return the OID of the index supporting
11419-
*the constraint, as well as the opclasses associated with the index
11420-
*columns.
11417+
*Validate that the 'attnums' columns in the 'pkrel' relation are valid to
11418+
*reference as part of a foreign key constraint.
11419+
*
11420+
*Returns the OID of the unique index supporting the constraint and
11421+
*populates the caller-provided 'opclasses' array with the opclasses
11422+
*associated with the index columns.
11423+
*
11424+
*Raises an ERROR on validation failure.
1142111425
*/
1142211426
static Oid
1142311427
transformFkeyCheckAttrs(Relation pkrel,
1142411428
int numattrs, int16 *attnums,
11425-
Oid *opclasses) /* output parameter */
11429+
Oid *opclasses)
1142611430
{
1142711431
Oidindexoid = InvalidOid;
1142811432
boolfound = false;

0 commit comments

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