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

Questions / Help
1 
 ** (Ecto.ConstraintError) constraint error when attempting to update struct:
     
         * nick_name (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 defined the following constraints:
     
         * nick_name_or_id (unique_constraint)
     
     code: {:error, error} = UserHandler.update_user(params)
     stacktrace:
       (ecto 3.4.4) lib/ecto/repo/schema.ex:700: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
       (elixir 1.11.4) lib/enum.ex:1411: Enum."-map/2-lists^map/1-0-"/2
       (ecto 3.4.4) lib/ecto/repo/schema.ex:685: Ecto.Repo.Schema.constraints_to_errors/3
       (ecto 3.4.4) lib/ecto/repo/schema.ex:666: Ecto.Repo.Schema.apply/4
       (ecto 3.4.4) lib/ecto/repo/schema.ex:348: anonymous fn/15 in Ecto.Repo.Schema.do_update/4


  def changeset(user, params) do
    cast(user, params, [
      :id,
      :email,
      :first_name,
      :last_name,
      :nick_name,
    ])
    |> unique_constraint([:nick_name, :id],
      name: "nick_name_or_id",
      match: :suffix,
      message: "nick_name_or_id_already_taken"
    )

help me out

what should be the name inside opts
my test is getting passed for name: “nick_name”
but getting the above error for name: “nick_name_or_id”

2

Pretty sure the :name option value should be an atom:

unique_constraint([:nick_name, :id],
      name: :nick_name_or_id,
      match: :suffix,
      message: "nick_name_or_id_already_taken"
    )

You can see the examples at: Ecto.Changeset — Ecto v3.9.1

3 
unique_constraint([:nick_name, :id],
      name: :nick_name_or_id,  --> gettiign the error
      match: :suffix,
      message: "nick_name_or_id_already_taken"
    )

unique_constraint([:nick_name, :id],
      name: :nick_name, --> not getting the error
      match: :suffix,
      message: "nick_name_or_id_already_taken"
    )

I am so confused why this weird behaviour is happening
and what is the role of name: in opts

4

What happens when you remove the :match option? (I didn’t take that into account in my previous answer).

For example, in a project of mine I have a Changeset function defined as:

defp validate_common(changeset) do
    validate_required(changeset, [
      :access_account_id,
      :identity_type_id,
      :account_identifier
    ])
    |> unique_constraint(:validates_identity_id, name: :syst_identities_validates_identities_udx)
  end

In the database I defined that constraint as:

validates_identity_id
        uuid
        CONSTRAINT syst_identities_validates_identities_udx UNIQUE

As you can see I’m using that full constraint name as the :name value in the Changeset, but I’m letting unique_constraint/3 default to :exact match.

5

getting the error after removing the :match option

6

So to be clear:

unique_constraint([:nick_name, :id],
      name: :nick_name_or_id,
      message: "nick_name_or_id_already_taken"
    )

is still giving you the error?

7

I think you’re missing the point here with :name. unique_constraint/3 is basically a nicety that converts errors that would be spurred by your unique constraint created on the database via a migration. When you create an unique constraint via a migration, you are given the option to name it. See Ecto.Migration — Ecto SQL v3.9.0 for more details. The :name here that you provide to in your change must match the one given on the migration or the one that’s automatically generated for you.

I suspect it works in the first instance between the :name aligns with what you have. You need to check what your unique index is first and update that accordingly.

8

yes, giving the error

9

A point of clarification. It’s true that most people will be using migrations, but unique_constraint/3 is not limited to that use case.

My application doesn’t use Ecto migrations at all and unique_constraint/3 works just fine when processing Changesets. You have to match the name of the constraint in the database using the :name option for unique_constraint/3, but that’s all custom constraint naming via migrations is doing anyway… setting the name of the constraint in the database.

I don’t disagree with anything else you’ve said in the context of migrations and, as I said earlier, that will be the most common case. But if you’re not using Ecto migrations, you can still get the benefits of clean Changesets with unique_constraint/3 so long as you call it correctly.

10

Oh certainly, you can definitely use unique_constraint/3 with other indexes you’ve created prior. At this point, I think our point regarding how the :name has to align is somewhat lost traction on the poster.

11

I think I’d need to see more to help further. Assuming you’re using Ecto migrations, I’d want to see the table definition, the revised code, and the full error message. As far as I can tell from what I’ve got in the thread above it should probably be working; so either I’m overlooking something or there’s a fact that missing.

When I’ve gotten the error message you originally posted, I fixed it the way I described (actually, the example I posted was originally a failed test found when the error you originally posted came up).

[EDIT[

I see the mistake I was making in reading the original error message you posted.

As @beepbeepbopbop correctly points out, you likely have a table migration which is defining a unique constraint, in this case :nick_name. The Changeset call to unique_constraint/3 must match that name (:nick_name).

I was reading the error wrong by mistaking the second part of the error as identifying the name you have to use in the Changeset. The first part of the error is the part I should have been looking at and was the part saying which name had to be used in unique_constraint/3.

Again, as was pointed out by @beepbeepbopbop, your Changeset name has to match the constraint name used when the constraint was defined in the migrations. That’s the point of the :name option… to allow you to make that name match.

12

what if there are multiple field in the constraint name, what should be the naming convention

unique_constraint([:nick_name, :id, :last_name, :first_name],
      name: ??, --> what should be their
      message: "nick_name_or_id_already_taken"
)
13

This depends on how you’ve defined the table your Ecto migrations. Remember that :name option in unique_constraint/3 is the name of the constraint, not the name of the fields. Can you show the code for the table migration, in particular where the unique index for those columns is created is the important part. The reason is that the name comes from the place where the unique index is created.

This blog post seems to show a reasonable explanation:

https://alvinrapada.medium.com/creating-unique-constraint-index-ecto-migration-elixir-37146722e593

14

actually there are 3 different migration file

in file 1

defmodule MyModule.Repo.Migrations.AddInitialSchema do
  use Ecto.Migration

  def up do
    execute """
      CREATE TABLE IF NOT EXISTS `users` (
        ...
        PRIMARY KEY (`id`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    """
  end
end


in file 2
defmodule MyModule.Repo.Migrations.UpdateColumnNickNameToUnqiueConstrainInUser do
  use Ecto.Migration

  def up do
    execute """
      ALTER TABLE users ADD UNIQUE(nick_name);
    """
  end
end

defmodule IamService.Repo.Migrations.UpdateColumnIdToUniqueColumn do
  use Ecto.Migration

  def up do
    execute """
      ALTER TABLE users ADD UNIQUE(id);
    """
  end
end

these are three migration i have run

and my mysql users table look like this(only showing two field which is revelant)

+---------------------------+--------------+------+-----+---------+----------------+
| Field                     | Type         | Null | Key | Default | Extra          |
+---------------------------+--------------+------+-----+---------+----------------+
| id                        | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| nick_name                 | varchar(255) | YES  | UNI | NULL    |                |
+---------------------------+--------------+------+-----+---------+----------------+
15

OK… you’re kinda using migrations, but not in the way envisioned by earlier answers and you have two independent unique constraints defined.

First as aside, you don’t need ALTER TABLE users ADD UNIQUE(id); The earlier primary key designation ensures that the id column is unique/indexed by definition.

Forgetting the above for a moment, assume that you do have two columns with user defined unique constraints. The key here is not that you have a multicolumn unique constraint, but two single column unique constraints. In
a Changeset context this means you’d have two calls to unique_constraint/3: one call for nick_name and another for id.

The :name opt in unique_constraint/3 would need to mirror the name of the constraint in the database; in this case you’ve not explicitly named the constraints when you create them so the name would be whatever MySQL defaults them to. I’m not a regular user of MySQL, but the original error gives us a hint that MySQL defaults to the column name. This is why the following doesn’t get an error:

unique_constraint([:nick_name, :id],
      name: :nick_name
      match: :suffix,
      message: "nick_name_or_id_already_taken"
    )

It’s matching the constraint in the databse, but the fields are defined incorrectly. What matches your migrations is:

 def changeset(user, params) do
    cast(user, params, [
      :id,
      :email,
      :first_name,
      :last_name,
      :nick_name,
    ])
    |> unique_constraint(:nick_name,
      name: :nick_name,
      message: "nick_name_already_taken"
    )
    |> unique_constraint(:id,
      name: :id,
      message: "id_already_taken"
    )

Again, the id primary key column shouldn’t be defined as a unique column either via an ALTER TABLE or in the Changeset since the PRIMARY KEY designation does everything for you in both the database and in Ecto.

1 Like