Composite unique constraint error while updating the changeset

Questions / Help
, , ,
1

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
2

Repo.insert_or_update depends on the state of the struct passed to it - in this case, it’s always going to be a “new” struct from Ecto.build_assoc so insert will always be attempted.

If what you want is “update the existing record if one matches the constraints or insert a new one”, consider the on_conflict option to Repo.insert.

3 Likes
3

To update to work, Try loading the record you are going to update first.

Check this link for more details:

https://hexdocs.pm/ecto/Ecto.Multi.html#insert_or_update/4

2 Likes
4

yeah…It worked by using on_conflict and conflict_target for contraint columns as option in Ecto.Multi.insert