Ecto composite primary key or unique_index for many-to-many?

Questions / Help
, , ,
1

I’ve been setting up a many-to-many relationship between two tables, actors and entities. Following the documentation here Ecto Association Guide - many to many.

I constructed my migration for the join table (which Is kept schema-less) like this:

    create table(:actors_entities, primary_key: false) do
      add(:actor_id, references(:actors, column: :id, type: :uuid),
        on_delete: :delete_all,
        primary_key: true
      )

      add(:entity_id, references(:entities, column: :id, type: :uuid),
        on_delete: :delete_all,
        primary_key: true
      )
    end

    create(index(:actors_entities, [:actor_id]))
    create(index(:actors_entities, [:entity_id]))

    create(
      unique_index(:actors_entities, [:actor_id, :entity_id],
        name: :actor_id_entity_id_unique_index
      )
    )

My migration is different from the example as I’m specifying a composite primary key as part of the :actors_entities table creation. In this case, do I still need the indexes and the unique_index constraint for anything?

1 Like
2

What a hot topic this was! :cold_face:

I ended up removing the unique index definition as it likely is redundant when relying on a composite primary key. I did however keep the indexes for making sure lookups using either of actor_id or entity_id alone are optimized.

1 Like