Thread: Generated names (suffix) for constraints not described in docs
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
On Wednesday, November 13, 2024, "Сергей П (SergeiDos)" <podrezov.sergey@gmail.com> wrote:
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.
Correct. Which means the specific name chosen is an implementation detail that can change at any time and should not be relied upon by the DBA. It could be a randomly generated uuid for all it matters, but we do make some attempt to make it readable.
Since they are user-facing values I do see some benefit to defining what is being seen, though precisely how and to what purpose I am unsure. If you see a name it seems self-describing what it means if you have familiarity with relational databases. Telling a user what they will get when they execute SQL without specifying a name is not something I would want to document.
David J.
"David G. Johnston" <david.g.johnston@gmail.com> writes: > On Wednesday, November 13, 2024, "Сергей П (SergeiDos)" < > podrezov.sergey@gmail.com> wrote: >> 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. > Correct. Which means the specific name chosen is an implementation detail > that can change at any time and should not be relied upon by the > DBA. Indeed. I double-checked the SQL spec and found that it says 3) If <constraint name definition> is not specified, then a <constraint name definition> that contains an implementation- dependent <constraint name> is implicit. The assigned <constraint name> shall obey the Syntax Rules of an explicit <constraint name>. "Implementation-dependent" is standards-ese for "implementations don't have to document their exact behavior". (If it said "implementation-defined", that would mean we should.) So not documenting it is expected. I think we have indeed changed the details around this in the past. regards, tom lane
Re: Generated names (suffix) for constraints not described in docs
From
""Сергей П (SergeiDos)""
Date:
Thank you for your reply!
Perhaps it would be worthwhile to record the rules for forming the names of indexes and constraints in the database in the documentation and in the algorithms.
In our project, we use the PostgreSQL database. And we would like to describe the rules for naming indexes and constraints for developers so that they are unambiguous. But the database allows you to declare constraints without a name. And in this case, the name will be formed according to some rules described in the algorithms in the source codes of the database. And, it seems, this algorithm should be recorded and described in the documentation. It would be logical if we formed the rules for our database developers identical to the internal automatic rules for forming names in the database, so that developers who choose the path of automatic name formation would receive the same result as those who set it manually in accordance with the described rules.
Thank you for your time,
Podrezov Sergey
13 нояб. 2024 г., в 19:22, David G. Johnston <david.g.johnston@gmail.com> написал(а):On Wednesday, November 13, 2024, "Сергей П (SergeiDos)" <podrezov.sergey@gmail.com> wrote: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.Correct. Which means the specific name chosen is an implementation detail that can change at any time and should not be relied upon by the DBA. It could be a randomly generated uuid for all it matters, but we do make some attempt to make it readable.Since they are user-facing values I do see some benefit to defining what is being seen, though precisely how and to what purpose I am unsure. If you see a name it seems self-describing what it means if you have familiarity with relational databases. Telling a user what they will get when they execute SQL without specifying a name is not something I would want to document.David J.
On Thu, Nov 14, 2024 at 3:32 AM "Сергей П (SergeiDos)" <podrezov.sergey@gmail.com> wrote:
It would be logical if we formed the rules for our database developers identical to the internal automatic rules for forming names in the database, so that developers who choose the path of automatic name formation would receive the same result as those who set it manually in accordance with the described rules.
I think if you want a specific, known, name, you should set it yourself. As stated earlier, the name itself is not important and the algorithm is subject to change - the fact that the name is based off of the table is a courtesy.
If there were no way to name your own object, I'd be more sympathetic to this request, but automatic naming is quite avoidable.
Cheers,
Greg