Ecto delete many_to_many join whilst keeping joined records in tact

Question

I have two Ecto models: User and Skill, which are joined with a many_to_many association via a users_skills table:

create table(:users_skills, primary_key: false) do
  add :user_id, references(:users, on_delete: :nothing)
  add :skill_id, references(:skills, on_delete: :nothing)
end

create unique_index(:users_skills, [:user_id, :skill_id])

In the User schema there is:

many_to_many :skills, Skill, join_through: "users_skills"

And in the Skill schema there is:

many_to_many :users, User, join_through: "users_skills"

What I want to do is delete a user’s skills without deleting the User or Skill itself. Currently I’m trying this:

query = Ecto.assoc(current_user, :skills)
Repo.delete_all(query)

However it throws the error:

(foreign_key_violation) update or delete on table "skills" violates foreign key constraint "users_skills_skill_id_fkey" on table "users_skills"

When adding on_delete: :delete_all to the users_skills migration, this has the undesired effect of deleting the associated skills.

How do I approach this so that only the association record is deleted with both User and Skill staying in tact?

Answer 1

I'm sure there're better ways to do this but you can pass on_replace: :delete option to many_to_many macro in your schemas.

defmodule User do
  use Ecto.Schema

  schema "users" do
    field(:name)
    many_to_many(:skills, Skill, join_through: "users_skills", on_replace: :delete)

    timestamps()
  end
end

Now if you run

current_user
|> Repo.preload(:skills)
|> Ecto.Changeset.change()
|> Ecto.Changeset.put_assoc(:skills, [])
|> Repo.update!()

It deletes from users_skills table and skills table will remain intact.