Primary ID unique constraint exception on ecto upsert

Questions / Help
1

I’m trying to implement an upsert/2 function to my entities on a Phoenix context. For example I’ll show only one schema.

Firstly, here’s the migration for the tag table:

defmodule Pescarte.Repo.Migrations.CriaTag do
  use Ecto.Migration

  def up do
    create table(:tag) do
      add :etiqueta, :string, null: false
      add :id_publico, :string

      add :categoria_id, references(:categoria), null: false

      timestamps()
    end

    create unique_index(:tag, [:etiqueta])
    create index(:tag, [:categoria_id])

    alter table(:midia) do
      remove :tags
    end
  end

  def down do
    drop_if_exists table(:tag)
    drop_if_exists unique_index(:tag, [:etiqueta])
    drop_if_exists index(:tag, [:categoria_id])

    alter table(:midia) do
      add :tags, {:array, :string}, null: false
    end
  end
end

And now this is the schema built with ecto:

defmodule Pescarte.Domains.ModuloPesquisa.Models.Midia.Tag do
  use Pescarte, :model

  alias Pescarte.Domains.ModuloPesquisa.Models.Midia.Categoria

  @type t :: %Tag{id: integer, etiqueta: binary, id_publico: binary, categoria: Categoria.t()}

  @required_fields ~w(etiqueta categoria_id)a

  schema "tag" do
    field :etiqueta, :string
    field :id_publico, Pescarte.Types.PublicId, autogenerate: true

    belongs_to :categoria, Categoria

    timestamps()
  end

  @spec changeset(map) :: {:ok, Tag.t()} | {:error, changeset}
  def changeset(tag \\ %__MODULE__{}, attrs) do
    tag
    |> cast(attrs, @required_fields)
    |> validate_required(@required_fields)
    |> unique_constraint(:etiqueta)
    |> foreign_key_constraint(:categoria_id)
    |> apply_action(:parse)
  end
end

And finally, my current upsert/2 implementation, which receives in the first optional argument the current schema and then their attributes! For a newly insertion, I omit the first argument with fallbacks for a default schema with nil fields. For updates, I pass the current schema in the first argument and newly attributes to update:

defmodule Pescarte.Domains.ModuloPesquisa.Repository do
  @behaviour IManageRepository

  defp on_conflict do
    {:replace_all_except, [:id, :id_publico]}
  end

  @impl true
  def upsert_tag(tag \\ %Tag{}, attrs) do
    with {:ok, tag} <- Tag.changeset(tag, attrs) do
      Repo.insert(tag, on_conflict: on_conflict(), conflict_target: [:etiqueta])
    end
  end
end

The above implementation works on insert statements, however when I try to update an existing tag, I receive this PK error:

** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * "tag_pkey" (unique_constraint)

If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `unique_constraint/3` on your changeset with the constraint
`:name` as an option.

The changeset has not defined any constraint.

    (ecto 3.10.1) lib/ecto/repo/schema.ex:804: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
    (elixir 1.14.2) lib/enum.ex:1658: Enum."-map/2-lists^map/1-0-"/2
    (ecto 3.10.1) lib/ecto/repo/schema.ex:788: Ecto.Repo.Schema.constraints_to_errors/3
    (ecto 3.10.1) lib/ecto/repo/schema.ex:769: Ecto.Repo.Schema.apply/4
    (ecto 3.10.1) lib/ecto/repo/schema.ex:377: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4

What it really means? The :on_conflict option didn’t resolves already?

2

This limits the conflict target to the unique constraint on the :etiqueta column, but you’re getting an error on the unique constraint on the id column.

As a best practice if you already have the id of the tag you should just issue an update statement instead of trying to insert.

EDIT: Ecto even has a function for this Ecto.Repo — Ecto v3.11.1

2 Likes
3

Indeed! So this raises another doubt, so in which cases the upsert option from Repo.insert/2 would be used, as described in docs: Ecto.Repo — Ecto v3.10.2

4

Hey @zoedsoupe I don’t understand your question. Did you understand what I was saying regarding conflict target?

The general use case for upsert is when you are trying to insert something that may already be there based on a non primary key value.