CODEWITHSUNDEEP
postgresqlelixirphoenix-frameworkectounique-constraint
SmartieHead
SmartieHead

Reputation: 63

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)

      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

Upvotes: 0

Views: 438

Answers (2)

SmartieHead
SmartieHead

Reputation: 63

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] )

Upvotes: 0

Jos&#233; Valim
Jos&#233; Valim

Reputation: 51369

You wrote:

the output should be basically updating the table and returning two fa details updated message.

But the code returns:

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

Look how it says action: :insert. So you are not updating, but inserting, which explain the error.

insert_or_update will only update a record if the record was loaded from the database. In your code, you are building records from scratch, and therefore they will always be an insert. You need to use Repo.get or similar to fetch them before passing them to the changeset so you can finally call insert_or_update.

Upvotes: 1

Related Questions