Ecto.ConstraintError - constraint error when attempting to insert struct

# in my migration
 create table(:users_account) do
      add :name, :string
      timestamps()
end
create unique_index("users_account", [:tag])

# in schema
schema "users_account" do
    field(:name, :string)
    timestamps()
  end

  def changeset(clash_accounts, attrs) do
    clash_accounts
    |> cast(attrs, [:name, :tag, :townhall])
    |> validate_required([:name, :tag, :townhall])
    |> unique_constraint(:name,
      name: :user_tag_index,
      message: "USER_EXISTS_WITH_SAME_NAME"
    )
  end

however, I get:

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

    * user_tag_index (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.

You probably should explicitly specify the user_tag_index name in the migration.

Can you check the Postgres users_account table and see if the unique index is called that or something else?

How can I specify it?

It may also be that you are inserting a User struct directly and not a changeset. I recall running into this before and thinking the index name was incorrect but I was not inserting a changeset.

Ecto has a default naming configuration for indexes.

https://hexdocs.pm/ecto_sql/Ecto.Migration.html#index/3

:name - the name of the index. Defaults to "#{table}_#{column}_index".

If you still want to confirm the name of the index like @dimitarvp said, use psql cmdline tool to login into the database.

{DATABASE} => \d {DESIRED_TABLE_NAME}
..... Displays a list of information.....
Indexes:
    "users_pkey" PRIMARY KEY, btree (id)
    "users_unique_index" UNIQUE, btree (name)

Get the name and define it as unique constraint in the changeset.

If you are using a GUI tool to manage databases, kindly check for relevant documentation related to finding out metadata for tables.

when migrating the table, its saying something about users_account_index. I tried using that at the constraint but same thing :confused: I have no clue now

@spizzy do you have a minimal public repo of the code?. It would be easier for anyone to jump in for helping you figure out the issue that you’re facing.

Also, posting the error message will provide more clarity on the issue.

You don’t have to set the name in the migration file, if you are planning to use default index name.

create unique_index(:users_account, [:tag]) should be enough in the migration file.

In the schema file,

def changeset(clash_accounts, attrs) do
    clash_accounts
    |> cast(attrs, [:name, :tag, :townhall])
    |> validate_required([:name, :tag, :townhall])
    |> unique_constraint(:tag,
      name: :users_account_tag_index,
      message: "USER_EXISTS_WITH_SAME_TAG"
    )
  end

Based on your previous inputs, this should likely fix your issue… For further help or queries, it’s better to provide some kind of a minimal repo that can be used to replicate the issue.

1 Like