Hello! I have written a small patch to modify the default names for foreign key constraints. Currently if the foreign key is composed of multiple columns we only use the first one in the constraint name. This can lead to similar constraint names when two foreign keys start with the same column. This sort of situation may commonly occur in a multi-tenant environment.
> CREATE TABLE users (tenant_id int, id int, PRIMARY KEY (tenant_id, id));
> CREATE TABLE posts (tenant_id int, id int, PRIMARY KEY (tenant_id, id));
> CREATE TABLE comments (
tenant_id int,
id int,
post_id int,
commenter_id int,
FOREIGN KEY (tenant_id, post_id) REFERENCES posts,
FOREIGN KEY (tenant_id, commenter_id) REFERENCES users
)
> \d comments
Table "public.comments"
Foreign-key constraints:
"comments_tenant_id_fkey" FOREIGN KEY (tenant_id, commenter_id)
REFERENCES users(tenant_id, id)
"comments_tenant_id_fkey1" FOREIGN KEY (tenant_id, post_id)
REFERENCES posts(tenant_id, id)
The two constraints have nearly identical names. With my patch the default names
will include both column names, so we have we will instead have this output:
Foreign-key constraints:
"comments_tenant_id_commenter_id_fkey" FOREIGN KEY (tenant_id,
commenter_id) REFERENCES users(tenant_id, id)
"comments_tenant_id_post_id_fkey" FOREIGN KEY (tenant_id, post_id)
REFERENCES posts(tenant_id, id)
This makes the default names for foreign keys in line with the default names
for indexes. Hopefully an uncontroversial change!
The logic for creating index names is in the function ChooseIndexNameAddition
in src/backend/commands/indexcmds.c. There is also similar logic fore creating
names for statistics in ChooseExtendedStatisticNameAddition in
src/backend/commands/statscmds.c.
I pretty much just copied and pasted the implementation from
ChooseIndexNameAddition and placed it in src/backend/commands/tablecmds.c.
The new function is called ChooseForeignKeyConstraintNameAddition. I updated
the comments in indexcmds.c and statscmds.c to also reference this new function.
Each of the three versions takes in the columns in slightly different forms, so
I don't think creating a single implementation of this small bit of logic is
desirable, and I have no idea where such a util function would go.
Regression tests are in src/test/regress/sql/foreign_key.sql. I create two
composite foreign keys on table, one via the CREATE TABLE statement, and the
other in a ALTER TABLE statement. The generated names of the constraints are
then queried from the pg_constraint table.
This is my first submission to Postgres, so I'm not entirely sure what the
protocol is here to get this merged; should I add this patch to the 2019-03
Commitfest?
Happy to hear any feedback!
- Paul Martinez
foreign-key-constraint-names-v1.patch
Description: Binary data
