Create index already exists

System Information
  • Strapi Version: 4.1.5
  • Operating System: Ubuntu
  • Database: PostgreSQL
  • Node Version: 14.18.2
  • NPM Version: 6.14.15
  • Yarn Version:

Hello,

Just created a new Strapi 4.1.5 app.

I’ve created a component tp-information under readings-information and on that component I’ve tried to create a one-to-one relational field called reference-cell i.e. tp_information has one reference cell. I’ve created other components of which have one-to-one relational fields with different collection types.

    "reference_cell": {
      "type": "relation",
      "relation": "oneToOne",
      "target": "api::reference-cell.reference-cell"
    }

However for this one (reference-cell), I am getting a create index already exists error.

The full error message is:

[
  {
    sql: 'create table "public"."components_readings_information_tp_informations_reference_cell_links" ("tp_information_id" integer null, "reference_cell_id" integer null)',
    bindings: []
  },
  {
    sql: 'create index "components_readings_information_tp_informations_reference_cell_links_fk" on "public"."components_readings_information_tp_informations_reference_cell_links" ("tp_information_id")',
    bindings: []
  },
  {
    sql: 'create index "components_readings_information_tp_informations_reference_cell_links_inv_fk" on "public"."components_readings_information_tp_informations_reference_cell_links" ("reference_cell_id")',
    bindings: []
  }
]
[2022-03-20 11:20:08.690] debug: ⛔️ Server wasn't able to start properly.
[2022-03-20 11:20:08.692] error: create index "components_readings_information_tp_informations_reference_cell_links_fk" on "public"."components_readings_information_tp_informations_reference_cell_links" ("tp_information_id") - relation "components_readings_information_tp_informations_reference_cell_" already exists
error: create index "components_readings_information_tp_informations_reference_cell_links_fk" on "public"."components_readings_information_tp_informations_reference_cell_links" ("tp_information_id") - relation "components_readings_information_tp_informations_reference_cell_" already exists

What I’ve tried

  • Creating a new database with and without this relational field (runs perfectly fine without)
  • Deleting the reference cell collection type and adding it again and adding the relational field to the component

Note that there is no error if I remove this relational field and there are other component of which have relational fields with different collection-types.

Previously using 3.6.9 where this error did not occur. Have not done a migration but have re-created the collection-types and components as part of another exercise.

Thank you

1 Like

Have raised bug Create index already exists: Relational field on component · Issue #13031 · strapi/strapi · GitHub

1 Like

Hi
I get the same problem. And now strapi will not start.
While waiting for the bug to be fixed I need a workaround.
I guess I need to manually edit some files to get strapi started again. But what files and what to change ?

[2022-06-28 15:52:47.823] debug: :no_entry: Server wasn’t able to start properly.
[2022-06-28 15:52:47.826] error: create index “person_network_membership_roles_entity_newtork_membership_links_fk” on “person_network_membership_roles_entity_newtork_membership_links” (“person_network_membership_role_id”) - relation “person_network_membership_roles_entity_newtork_membership_links” already exists
error: create index “person_network_membership_roles_entity_newtork_membership_links_fk” on “person_network_membership_roles_entity_newtork_membership_links” (“person_network_membership_role_id”) - relation “person_network_membership_roles_entity_newtork_membership_links” already exists
at Parser.parseErrorMessage

error Command failed with exit code 1.
info Visit yarn run | Yarn for documentation about this command.

Hey, I got the same issue. and I tried changing the collectionName manually (in my case, I deleted some words) to a shorter one. It worked for me, good luck @terchris

1 Like

Same issue here! any update on this ?

The way I fix this issue is removing the following index from a data base and then it started working for me

DROP INDEX  "articles_categories_links_inv_fk";

It look like the strapi server everytime it started it create index for relation collection so dropping the index solved it.

This looks to be a limitation of Postgres, where the max length of an identifier (including table and index names) can be 63.

And when creating the relation on the component, strapi concatenates component name, and the referenced collection’s name, that can easily become too long.

E.g. I had the issue with this name: components_fm_fm_how_to_article_components_fm_how_to_article_links_fk

As @rendysyabany mentioned shortening the collection name and/or folder name can help, and as an extra, once you successfully created the collection with the link, you can rename the display_name of the component, so you can have it changed.

4 Likes

@Barney :100: Thank you!

I had the exact same issue, shortening the name of the component fixed it immediately.

The name of the component that held the relationship was too long.

When Strapi builds the database it creates foreign keys by appending the component name to the collection type name.

An extreme example:
Component components_aggregators_sci_fi_movie_director_interviews references, with a one to many link, the collection interviews_with_sci_fi_directors.

At build this attempts to create a foreign key index by combining these together and adding _links_fk to give something along the lines of: components_aggregators_sci_fi_movie_director_interviewsinterviews_with_sci_fi_directors_links_fk
Which is 96 characters long. PostgreSQL only allows up to 63 characters (63 bytes) and then truncates the rest. So it ends up with components_aggregators_sci_fi_movie_director_interviewsintervie. If I understand it correctly, another index is also created for the primary key. Again trying to use the same combination and again resulting in the truncated version which is now a duplicate and throws the error:
create index "components_aggregators_sci_fi_movie_director_interviewsinterviews_with_sci_fi_directors_links_fk" on "components_aggregators_sci_fi_movie_director_interviewsinterviews_with_sci_fi_directors_links" ("components_aggregators_sci_fi_movie_director_interviews_id") - relation "components_aggregators_sci_fi_movie_director_interviewsintervie" already exists

I wasted many hours of debugging before discovering this. Shortening the component name removed the issue immediately.

1 Like