confusing error message on constraint error

Question

Ecto is throwing me the following error:

** (exit) an exception was raised:
    ** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * unique: res_users_login_key

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: res_users_login_index

Do I understand correctly that the actual name of the constraint in postgresql determines if the unique_constraint/3 function is successful or not? Just for reference, in postgreSQL the constraint is defined as follows:

Indexes:
    "res_users_pkey" PRIMARY KEY, btree (id)
    "res_users_login_key" UNIQUE CONSTRAINT, btree (login)

so _key and not _index.

I call the constraint function as follows:

 changeset |> unique_constraint(:login)

So, how do I make this work?

Try `|> unique_constraint(:login, [name: :res_users_login_index])`. — Dogbert, Apr 28 '17 at 14:22
Do you mean: `|> unique_constraint(:login, [name: :res_users_login_key])`? — raarts, Apr 28 '17 at 14:23
Oops yes, `_key`. `_index` is the incorrect name used by default since you hadn't specified a `name`. — Dogbert, Apr 28 '17 at 14:29
Looking at http://stackoverflow.com/questions/4107915/postgresql-default-constraint-names/4108266#4108266 `_index` seems to be an inappropriate default doesn't it? Seems `_key` would be better for unique constraints. — raarts, Apr 28 '17 at 14:35
They probably default to that because `unique_index` in Ecto migrations use the `_index` suffix for the index names by default. — Dogbert, Apr 28 '17 at 14:41

score 3 · Accepted Answer · answered Apr 28 '17 at 14:37

The constraint name used by Ecto when one is not given is #{table_name}_#{field}_index. Your table is probably named res_users, and the field is login, which is why Ecto uses the constraint name res_users_login_index, which is incorrect in your case. You'll need to explicitly specify the name res_users_login_key in the call to unique_constraint:

|> unique_constraint(:login, [name: :res_users_login_key])

confusing error message on constraint error

1 Answers1