Primary Keys and Foreign Keys are not listed on Indexes node

[luronumen]

ACTUAL RESULTS

• Primary Keys and Foreign Keys are NOT listed on Indexes node
• Indexes node is wrongly named as Indices;

EXPECTED RESULTS

• Primary Keys and Foreign Keys should be listed on Indexes node
• Indexes node should be named as Indexes;

[Jdochoa]

Hi *

The indices from Primary and Foreign key it's a system index, i think the correct is create a system indices node. What do you think @mariuz , @arvanus ?

The plural of the noun index should always be indices. This handily distinguishes it from the present tense of the verb index, which can only be indexes. ./jo

[luronumen]

Hi @Jdochoa

Indexes created for Primary/Unique/Foreign keys are automatically named by firebird when users do NOT name them when creating tables:

CREATE TABLE B
(
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY,
  B_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
  UNIQUE (B_NAME)
);

For more details about this behavior: Names for Constraints and Their Indexes

But when the user decides to create Primary/Unique/Foreign keys name them, these indexes are given the name defined by the user:

CREATE TABLE A
(
  ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  A_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR,
  CONSTRAINT A_PK PRIMARY KEY (ID),
  CONSTRAINT A_UK_1 UNIQUE (A_NAME)
);

For more details about this behavior: Named Constraints

It is a good practice to always define friendly names for Primary/Unique/Foreign keys of tables because this facilitates the identification of where any errors of insertion or updating of tables happened.

Having said that, in my opinion moving the indexes created by Primary/Unique/Foreign keys to a System Indexes node can give a false illusion that the user would not have the control to name them when are creating tables. What do you think?

Regarding indices vs. indexes, I am still confused about using Indices or Indexes but for more details follow this link: Indices vs. Indexes

Best Regards, Luciano

[krilbe]

Den 2021-03-05 kl. 11:27, skrev Luciano Mendes:

Hi @Jdochoa https://github.com/Jdochoa

Indexes created for Primary/Unique/Foreign keys are automatically named by firebird when users do NOT name them when creating tables:

|CREATE TABLE B ( ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL PRIMARY KEY, B_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR, UNIQUE (B_NAME) ); |

But when the user decides to create Primary/Unique/Foreign keys name them, these indexes are given the name defined by the user:

|CREATE TABLE A ( ID BIGINT GENERATED BY DEFAULT AS IDENTITY NOT NULL, A_NAME VARCHAR(32) CHARACTER SET WIN1252 NOT NULL COLLATE WIN_PTBR, CONSTRAINT A_PK PRIMARY KEY (ID), CONSTRAINT A_UK_1 UNIQUE (A_NAME) ); |

It is a good practice to always define friendly names for Primary/Unique/Foreign keys of tables because this facilitates the identification of where any errors of insertion or updating of tables happened.

Having said that, in my opinion moving the indexes created by Primary/Unique/Foreign keys to a /System Indexes/ node can give a false illusion that the user would not have the control to name them when are creating tables. What do you think?

My perspective as a user: As far as I understand, primary keys, foregin keys as well as unique constraints consist of both a constraint and a supporting index. The user can opt to specify a name or not to do so. If not specified, FB will auto generate a name for each (are the names identical in this case or not?). If the user does specify a name for the constraint, that name will be applied to both the constraint and the supporting index. Right?

As a user I would expect to find these supporting indexes "somewhere". If they are grouped under a separate node for such "implicit" indexes or listed along with manually created "explicit" indexes is not very important to me, but if using separate nodes I think I would like those nodes to be listed next to each other in the tree.

Regarding indices vs. indexes, I am still confused about using Indices or Indexes but for more details follow this link: Indices vs. Indexes https://www.grammar.com/indices_vs._indexes

I have no strong opinion here.

Regards, Kjell Rilbe

[luronumen]

Hi @krilbe

There is no difference between what you call "implicit" and "explicit" indexes. They are all stored in the same RDB$INDICES system table. The only difference is that if you don't choose a name for the table constraint, firebird will choose the index name for that constraint.

Best Regards, Luciano

[krilbe]

Den 2021-03-05 kl. 12:37, skrev Luciano Mendes:

Hi @krilbe https://github.com/krilbe

There is no difference between what you call "implicit" and "explicit" indexes. They are all stored in the same RDB$INDICES system table. The only difference is that if you don't choose a name for the table constraint, firebird will choose the index name for that constraint.

Of course. I just wanted to make sure I got it right. I thought the discussion was where to display the constraint-generated indexes inside FB's object tree...?

And in that context my point was that I'd like to see the constraint-generated indexes somewhere in FB's tree (not hide them), and that I don't care if they are listed alongside the explicit indexes or under a separate tree node, but if you opt to use two separate tree nodes, please place them next to each other.

Regards, Kjell

[arvanus]

I would let the user choose to show PK/FK (or no indices at all) in the Preferences panel

[arvanus]

About the constraint-generated indexes names, I think that it should follow the same rule of the user named indexes

[luronumen]

One more reason why the indexes of the primary and foreign keys of the tables should be shown inside the Indexes node and not in a System Index node: These indexes are shown in the index tab of the tables

Therefore, the most consistent way to fix these issues would be to simply add them to the index node instead of adding them to the preferences panel. Make sense for you @arvanus @mariuz and @Jdochoa ?

[luronumen]

Retest result on FlameRobin 0.9.3.12:

• ISSUE 1: Primary Keys and Foreign Keys are NOT listed on Indexes node: PASS (They are being shown in the System Indices node);
• ISSUE 2: Indexes node is wrongly named as Indices: FAIL!

Best Regards, Luciano