Hi! I’m trying to emulate an “enum” data type by forming a many-to-many relationship between 2 tables. Essentially I have Users and they have Roles. The roles are seeded into the database, but rarely change. I’m using the following pattern to emulate enums for roles:

roles.ex

  @superhero %Role{
    id: 1,
    name: "superhero"
  }

  @none %Role{
    id: 2,
    name: "none"
  }

  def get_by_name("superhero"), do: @superhero
  def get_by_name("none"), do: @none
...

I have a many-to-many set up between user and role schemas:
user.ex

  schema "users" do
    field :name, :string, default: ""
    field :email, :string, default: ""
    
    many_to_many :roles, MyApp.Role,
      join_through: "user_roles", on_replace: :delete
  end

role.ex

  schema "roles" do
    field :name, :string, unique: true

    many_to_many :users, MyApp.User,
      join_through: "user_roles", on_replace: :delete
  end

I would like to be able to simply change the roles a user has using something like put_assoc without doing a database lookup for the actual role record. For example, I could change my get_by_name(name) function to do a database lookup for the actual record (In fact, changing this function to do exactly that works fine), but this seems inefficient and might cause performance troubles since this function will be used extensively throughout the codebase.

I also would prefer a solution that avoids directly modifying the join table user_roles, as that seems a bit cumbersome and counter to the intuitive/succinct “ecto way” of managing associations. I’m hoping there is an efficient way to specify an existing record (where I already know the id) to add to or modify the association collection, for example passing a (list of) struct(s) with the id of the role I want to associate.

I’ve tried the following code to do this, but it raises a unique_constraint error on the roles, which leads me to think it is trying to create a new record with the same id instead of associating the existing one.

    {:ok, user} =
      User.changeset(%User{
        email: "ironman@avengers.com",
        name: "Tony Stark",
      }, attrs)
      |> put_assoc(:roles, [MyApp.Roles.get_by_name("superhero")])
      |> Repo.insert

** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * roles_pkey (unique_constraint)

If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `unique_constraint/3` on your changeset with the constraint
`:name` as an option.

The changeset has not defined any constraint.

How can I get this to associate the existing record for superhero with id: 1, instead of trying to create a new one?

Any help or thoughts about how to best approach this would be greatly appreciated. Thanks!