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
