Ecto unique constraint error on pkey

Question

I started getting the following error when trying to insert a new room

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

    * unique: rooms_pkey

If you would like to convert this constraint into an error, please
call unique_constraint/3 in your changeset and define the proper
constraint name. The changeset defined the following constraints:

    * unique: rooms_name_index

Shouldn't the primary key auto-increment? What would make this error occur suddenly? The insert is done as part of a multi, with the relevant part being:

|> Multi.insert(:room, Room.changeset(%Room{}, %{name: "service-user-care-team:" <> Integer.to_string(user.id)}))

For additional reference, here's my schema including the changeset

schema "rooms" do
  field :name, :string
  many_to_many :users, App.User, join_through: "user_rooms", on_delete: :delete_all
  has_many :messages, App.Message

  timestamps()
end

def changeset(struct, params \\ %{}) do
  struct
  |> cast(params, [:name])
  |> validate_required([:name])
  |> unique_constraint(:name)
end

And here's the migration

defmodule App.Repo.Migrations.CreateRoom do
  use Ecto.Migration

  def change do
    create table(:rooms) do
      add :name, :string, null: false

      timestamps()
    end

    create unique_index(:rooms, [:name])
 end
end

Possible duplicate of [postgres autoincrement not updated on explicit id inserts](https://stackoverflow.com/questions/9108833/postgres-autoincrement-not-updated-on-explicit-id-inserts) — Katherine, Feb 19 '18 at 11:24

score 5 · Answer 1 · answered Feb 19 '18 at 11:23

I found why this was happening.

An important note that I forgot to include in the original description is that this happened while working in dev environment.

It's related to this answer. I had previously manually inserted some data with Postico, which must have included explicitly including the id. The pkey sequence wasn't updated at that point, which later caused a duplicate id to be set.

score 1 · Answer 2 · answered Oct 31 '18 at 16:51

tl;dr

Replace

|> unique_constraint(:name)

with

|> unique_constraint(:name, name: :rooms_pkey)

==============

unique_constraint doesn't exactly work as you'd expect.

In the example you posted, the atom :name is passed into unique_constraint. Quoting from the docs:

By default, the constraint name is inferred from the table + field. May be required explicitly for complex cases

This is why the changeset defines :rooms_name_index even though the actual index is :rooms_pkey. You must explicitly the :name option in order to avoid this default behaviour in ecto.

score 1 · Answer 3 · answered Jan 30 '23 at 17:46

Mine was probably a niche case, but I had records imported from another table a number of years ago with far higher ID's, that my version of postgres eventually caught up with.

I had to follow the step laid out here Reset auto increment counter in postgres, namely running:

ALTER SEQUENCE product_id_seq RESTART WITH X

directly on the postgres DB to reset the ID counter on the product table.

Ecto unique constraint error on pkey

3 Answers3