This is an automated email from the ASF dual-hosted git repository. leborchuk pushed a commit to branch REL_2_STABLE in repository https://gitbox.apache.org/repos/asf/cloudberry.git
commit bbc01e7b198a8483491ef8353089c0ce918953f7 Author: David Rowley <[email protected]> AuthorDate: Tue Jan 30 10:16:42 2024 +1300 Doc: mention foreign keys can reference unique indexes We seem to have only documented a foreign key can reference the columns of a primary key or unique constraint. Here we adjust the documentation to mention columns in a non-partial unique index can be mentioned too. The header comment for transformFkeyCheckAttrs() also didn't mention unique indexes, so fix that too. In passing make that header comment reflect reality in the various other aspects where it deviated from it. Bug: 18295 Reported-by: Gilles PARC Author: Laurenz Albe, David Rowley Discussion: https://www.postgresql.org/message-id/18295-0ed0fac5c9f7b17b%40postgresql.org Backpatch-through: 12 (cherry picked from commit 973e14028e85364d81c78270ff00440e21e281fd) --- doc/src/sgml/ddl.sgml | 20 ++++++++++---------- doc/src/sgml/ref/create_table.sgml | 19 +++++++++++++++---- src/backend/commands/tablecmds.c | 14 +++++++++----- 3 files changed, 34 insertions(+), 19 deletions(-) diff --git a/doc/src/sgml/ddl.sgml b/doc/src/sgml/ddl.sgml index 536f97a6be3..6579dd0b972 100644 --- a/doc/src/sgml/ddl.sgml +++ b/doc/src/sgml/ddl.sgml @@ -1083,16 +1083,16 @@ CREATE TABLE order_items ( <para> A foreign key must reference columns that either are a primary key or - form a unique constraint. This means that the referenced columns always - have an index (the one underlying the primary key or unique constraint); - so checks on whether a referencing row has a match will be efficient. - Since a <command>DELETE</command> of a row from the referenced table - or an <command>UPDATE</command> of a referenced column will require - a scan of the referencing table for rows matching the old value, it - is often a good idea to index the referencing columns too. Because this - is not always needed, and there are many choices available on how - to index, declaration of a foreign key constraint does not - automatically create an index on the referencing columns. + form a unique constraint, or are columns from a non-partial unique index. + This means that the referenced columns always have an index to allow + efficient lookups on whether a referencing row has a match. Since a + <command>DELETE</command> of a row from the referenced table or an + <command>UPDATE</command> of a referenced column will require a scan of + the referencing table for rows matching the old value, it is often a good + idea to index the referencing columns too. Because this is not always + needed, and there are many choices available on how to index, the + declaration of a foreign key constraint does not automatically create an + index on the referencing columns. </para> <para> diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml index 3a4920c4c35..b6871a2589e 100644 --- a/doc/src/sgml/ref/create_table.sgml +++ b/doc/src/sgml/ref/create_table.sgml @@ -1191,10 +1191,11 @@ Where column_reference_storage_directive is: column(s) of some row of the referenced table. If the <replaceable class="parameter">refcolumn</replaceable> list is omitted, the primary key of the <replaceable class="parameter">reftable</replaceable> - is used. The referenced columns must be the columns of a non-deferrable - unique or primary key constraint in the referenced table. The user - must have <literal>REFERENCES</literal> permission on the referenced table - (either the whole table, or the specific referenced columns). The + is used. Otherwise, the <replaceable class="parameter">refcolumn</replaceable> + list must refer to the columns of a non-deferrable unique or primary key + constraint or be the columns of a non-partial unique index. The user + must have <literal>REFERENCES</literal> permission on the referenced + table (either the whole table, or the specific referenced columns). The addition of a foreign key constraint requires a <literal>SHARE ROW EXCLUSIVE</literal> lock on the referenced table. Note that foreign key constraints cannot be defined between temporary @@ -2326,6 +2327,16 @@ CREATE TABLE cities_partdef </para> </refsect2> + <refsect2> + <title>Foreign Key Constraints</title> + + <para> + It is a <productname>PostgreSQL</productname> extension that a + foreign key constraint may reference columns of a unique index instead of + columns of a primary key or unique constraint. + </para> + </refsect2> + <refsect2> <title><literal>NULL</literal> <quote>Constraint</quote></title> diff --git a/src/backend/commands/tablecmds.c b/src/backend/commands/tablecmds.c index d21b2800577..10cb99402f7 100644 --- a/src/backend/commands/tablecmds.c +++ b/src/backend/commands/tablecmds.c @@ -13042,15 +13042,19 @@ transformFkeyGetPrimaryKey(Relation pkrel, Oid *indexOid, /* * transformFkeyCheckAttrs - * - * Make sure that the attributes of a referenced table belong to a unique - * (or primary key) constraint. Return the OID of the index supporting - * the constraint, as well as the opclasses associated with the index - * columns. + * Validate that the 'attnums' columns in the 'pkrel' relation are valid to + * reference as part of a foreign key constraint. + * + * Returns the OID of the unique index supporting the constraint and + * populates the caller-provided 'opclasses' array with the opclasses + * associated with the index columns. + * + * Raises an ERROR on validation failure. */ Oid transformFkeyCheckAttrs(Relation pkrel, int numattrs, int16 *attnums, - Oid *opclasses) /* output parameter */ + Oid *opclasses) { Oid indexoid = InvalidOid; bool found = false; --------------------------------------------------------------------- To unsubscribe, e-mail: [email protected] For additional commands, e-mail: [email protected]
