composite unique constraint error while updating the changeset

Question

I have a schema two_fa_details where answer and question_id are the fields and both are unique together.. Now when I am trying to insert data into it first it gets inserted but updating it next time isn't working.. It says constraint error.

I have a function set_two_factor_details written for updating table.. The function works fine for inserting the data very firsat time..but when iam updating it...its not working..i have a PUT API for this function. this is my migration file for schema two_fa_details

def change do
    create table(:two_fa_details) do
      add :answer, :string
      add :userprofile_id, references(:user_profile, on_delete: :nothing)
      add :question_id, references(:questions, on_delete: :nothing)

      timestamps()
    end

    create index(:two_fa_details, [:userprofile_id])
    create index(:two_fa_details, [:question_id])

    create unique_index(:two_fa_details, [:userprofile_id, :question_id], name: :user_twofa_detail)
  end

here is a snippet of code

def set_twofactor_details(client_id, twofa_records) do
    user = Repo.get_by(UserProfile, client_id: client_id)
    twofa_records = Enum.map(twofa_records, &get_twofa_record_map/1)

    Enum.map(twofa_records, fn twofa_record ->
      Ecto.build_assoc(user, :two_fa_details)
      |> TwoFaDetails.changeset(twofa_record)
    end)
    |> Enum.zip(0..Enum.count(twofa_records))
    |> Enum.reduce(Ecto.Multi.new(), fn {record, id}, acc ->
      Ecto.Multi.insert_or_update(acc, String.to_atom("twfa_record_#{id}"), record)
    end)|>IO.inspect()
    |> Ecto.Multi.update(
      :update_user,
      Ecto.Changeset.change(user, two_factor_authentication: true, force_reset_twofa: false)
    )
    |> Repo.transaction()|>IO.inspect()
    |> case do
      {:ok, _} ->
        {:ok, :updated}

      {:error, _, changeset, _} ->
        error_string = get_first_changeset_error(changeset)
        Logger.error("Error while updating TWOFA: #{error_string}")
        {:error, 41001, error_string}
    end
  end

the output should be basically updating the table and returning two fa details updated message. but in the logs its showing constraint error.please help me with this..Iam new to elixir.

{:error, :twfa_record_0,
 #Ecto.Changeset<
   action: :insert,
   changes: %{answer: "a", question_id: 1, userprofile_id: 1},
   errors: [
     unique_user_twofa_record: {"has already been taken",
      [constraint: :unique, constraint_name: "user_twofa_detail"]}
   ],
   data: #Accreditor.TwoFaDetailsApi.TwoFaDetails<>,
   valid?: false
 >, %{}}
[error] Error while updating TWOFA: `unique_user_twofa_record` has already been taken

Answer 1

I tried doing it by using upserts for ecto and it worked. here is a snippet of code to refer

Ecto.Multi.insert_or_update(acc, String.to_atom("twfa_record_#{id}"), record,
       on_conflict: :replace_all_except_primary_key,
       conflict_target: [:userprofile_id, :question_id] )