Composite unique constraint error while updating the changeset


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)


    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)

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 =, &get_twofa_record_map/1), fn twofa_record ->
      Ecto.build_assoc(user, :two_fa_details)
      |> TwoFaDetails.changeset(twofa_record)
    |> Enum.reduce(, fn {record, id}, acc ->
      Ecto.Multi.insert_or_update(acc, String.to_atom("twfa_record_#{id}"), record)
    |> Ecto.Multi.update(
      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}

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,
   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

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.


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

Check this link for more details:


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