agora inbox for pgsql-docs@postgresql.org  

help / color / mirror / Atom feed
From: "Сергей П (SergeiDos)" <podrezov.sergey@gmail.com>
To: pgsql-docs@lists.postgresql.org
Subject: Generated names (suffix) for constraints not described in docs
Date: Wed, 13 Nov 2024 17:06:12 +0500
Message-ID: <712E40A2-744F-424E-AE45-95CE5BE27D82@gmail.com> (raw)

 <https://translate.google.com/about/?hl=ru;
 <https://policies.google.com/?hl=ru; <https://support.google.com/translate/?hl=ru; <https://www.google.com/about?hl=ru;
 <https://translate.google.com/about/?hl=ru;
 <https://policies.google.com/?hl=ru; <https://support.google.com/translate/?hl=ru; <https://www.google.com/about?hl=ru;
Hello!
In the documentation for the Constraints section https://www.postgresql.org/docs/current/ddl-constraints.html there is a phrase: "So, to specify a named constraint, use the key word CONSTRAINT followed by an identifier followed by the constraint definition. (If you can't specify a constraint name in this way, the system choose a name for you.)"
But nowhere in the documentation are the rules by which it generates names on its own described.

For example, the code:
CREATE TABLE IF NOT EXISTS test_table_with_very_long_table_name_over_sixty_four_symbols
(
id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
code VARCHAR
);

CREATE TABLE IF NOT EXISTS test_table_2_with_very_long_table_name_over_sixty_four_symbols
(
 id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
 not_very_long_id_from BIGINT NOT NULL REFERENCES test_table_with_very_long_table_name_over_sixty_four_symbols (id),
 not_very_long_id_to BIGINT NOT NULL REFERENCES test_table_with_very_long_table_name_over_sixty_four_symbols (id)
);

empirically we find out that two constraints are generated:

test_table_2_with_very_long_table_name_not_very_long_id_to_fkey
test_table_2_with_very_long_table_na_not_very_long_id_from_fkey

Table name + column name + suffix _fkey
In this case, the table name is truncated so that the total length of the name is no more than 63 characters.

But the rules for forming this string are not described in the documentation.

The documentation also does not include other suffixes that the system generates:
pkey for a Primary Key constraint;
key for a Unique constraint;
excl for an Exclusion constraint;
idx for any other kind of index;
fkey for a Foreign key;
check for a Check constraint;
Standard suffix for sequences is
seq for all sequences

Best regards
Podrezov Sergey
 <https://translate.google.com/history; <https://translate.google.com/saved;
 <https://translate.google.com/history; <https://translate.google.com/saved>=

view thread (5+ messages)  latest in thread

Message-ID: <712E40A2-744F-424E-AE45-95CE5BE27D82@gmail.com>
Permalink:  ../712E40A2-744F-424E-AE45-95CE5BE27D82@gmail.com/
Also on:    postgresql.org/message-id/712E40A2-744F-424E-AE45-95CE5BE27D82@gmail.com

reply
Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-docs@postgresql.org
  Cc: podrezov.sergey@gmail.com, pgsql-docs@lists.postgresql.org
  Subject: Re: Generated names (suffix) for constraints not described in docs
  In-Reply-To: <712E40A2-744F-424E-AE45-95CE5BE27D82@gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox