Allow renaming auto-generated foreign key column names when creating a connection

[Nezteb]

Feature description When I connect two tables with a one-to-many relationship, the foreign key name on the recevier is automatically generated:

In the above screenshot, I want to be able to name the foreign key to user_id, not id_users.

I tried changing the settings for this connection, but nothing I try seems to do what I'd like:

If you try to edit the column directly, you can't because it is protected:

[dekiesel]

I am in the same boat. I have a table that has a relationship to itself (think employee->manager relationship), but the created foreign key is just called id_employee and I can't change the name to id_manager.

If anybody has a workaround please let me know.

[rkhaotix]

Does changing the name patterns doesn't work?

[dekiesel]

@rkhaotix I am not sure if you meant me, but: I am trying to change the name itself, not the pattern.

[rkhaotix]

@dekiesel Hi, yes. :)

Since the generated objects' names are ruled by the patterns you have to alter the patterns themselves to get the desired results.

In your case, you can hard-code "id_employee" as the column pattern.

[dekiesel]

@dekiesel Hi, yes. :)

Since the generated objects' names are ruled by the patterns you have to alter the patterns themselves to get the desired results.

In your case, you can hard-code "id_employee" as the column pattern.

Ah, thanks! :)

In the meantime I found that I can "convert" the relationship and with that I was able to rename the column too. Is that effectively the same or should I not have done that?

[rkhaotix]

@dekiesel The convert feature is an alternative way to do what you want. Anyway, there are some drawbacks by doing that. See here:

https://www.pgmodeler.io/support/docs/718-managing-relationship-generated-objects?v=1.1.0

[dekiesel]

That was an interesting read, thank you very much!!

[MarcelloSciarra]

Hi, I am having an issue which looks correlated. Not sure if it's worth a dedicated thread. I have generated a table with a very large name (57 characters, below the limit of 63). When I generate a relationship with another table, the automatically generated foreign key column name looks like this:

Even if the automatically generated name with the pattern {sc}{st} should be id{table_name} = 60 chars < 63.

I also tried to manually change the pattern to:

And I get the same issue. I then tried to convert the relationship

And then, again by manually modifying the pattern to the desired name, I was able to get the desired result:

So this does not seem to be due to the maximum number of 63 characters (or not only, at least, since this issue does not happen with significantly shorter names). Is this behaviour expected or is it a bug? I know I could work this around by simply user shorter names but I am importing an old model that worked with that name and now changing the tables' names would have a non-negligible impact.

Thanks!

[rkhaotix]

Hi @MarcelloSciarra

Unfortunately, this is a limitation of the tool. :(

pgModeler will automatically truncate object names that exceed 63 bytes. But it's not a simple truncation, it'll append six characters for disambiguation purposes.

Let me explain, say we're creating two tables whose names exceed the name length limits. For this example, let's say that the maximum allowed length for names is 20, so our tables will be named a_table_with_long_name_AAA and a_table_with_long_name_BBB.

Before adding the tables in the model, pgModeler does the following:

1. Truncates the names to the maximum allowed length:

• a_table_with_long_name_AAA is renamed to a_table_with_long_na
• a_table_with_long_name_BBB is also renamed to a_table_with_long_na
  See how their names are the same after the truncation?

2. Here is where the disambiguation procedure is applied. To avoid name conflicts, pgModeler removes the last 6 characters from the truncated names and appends 6 new characters coming from the md5 hash of the objects' original names, so, the new names will be:

• a_table_with_long_name_AAA -> a_table_with__099f4c
• a_table_with_long_name_BBB -> a_table_with__d4489e
  If we don't do this step, pgModeler will add the first table without problems, but when adding the second, it'll raise an error related to name conflicts.

Of course, this isn't the best solution because the disambiguation may fail in cases where the involved hashes have the same 6 first characters. But this way, we diminish the chance of name conflicts.

The solution you already deduced: for now, is to use shorter names. That's until I find a better solution.

[MarcelloSciarra]

Hi @rkhaotix,

Thanks for the detailed answer! I guess the only thing that is not fully clear to me is why the name is truncated in the first place if it does not exceed the 63 characters. And, again, this only happens when the name is auto-generated but it does not happen if I "convert" the relationship and define the exact same name manually, which is below 63 chars and it works just fine.

Any hint on that? Is the truncation performed if (length+6) > 63 when the name is automatically generated?

Thanks!

[rkhaotix]

Hi @rkhaotix,

Thanks for the detailed answer! I guess the only thing that is not fully clear to me is why the name is truncated in the first place if it does not exceed the 63 characters. And, again, this only happens when the name is auto-generated but it does not happen if I "convert" the relationship and define the exact same name manually, which is below 63 chars and it works just fine.

Any hint on that? Is the truncation performed if (length+6) > 63 when the name is automatically generated?

Hi @MarcelloSciarra

You are right! The disambiguation routine is not working well for long names. I'll patch it! ;)

[MarcelloSciarra]

Hi @rkhaotix,

great, thanks a lot! :)