Unique field with postgres and ecto

Hi everyone.
This should have been a no brainer, but somehow I can’t seem to get it to work.

I am using Ecto with postgres and have a table with a schema defined as

schema "results" do
  field :result, :string
  field :color,  :string
end

def changeset(struct, params) do
  struct
    |> cast(params, [:result, :color])
    |> validate_required([:result, :color])
    |> validate_format(:color, ~r/^#[0-9a-fA-F]{3}$|^#[0-9a-fA-F]{6}$/m) # Hex string of 3 or 6 valid hex characters.
    |> unique_constraint(:result)
end

As you can see, I am trying to enforce the result field to be unique.
This example was made directly following the example in the documentation: Validations and constraints.
In this case, the unique_constraint() just gets ignored and result is inserted into the db multiple times.

Then I have found this article, create unique_index(:results, :result) line in migration file:

def change do
  create table(:results) do
    add :result, :string
    add :color,   :string
  end

  create unique_index(:results, :result)
end

This seemed to work in sense that the result wasn’t inserted twice, but it caused an exception which crashes the application.

** (Mix) Could not start application myapp: MyApp.Application.start(:normal, []) returned an error: shutdown: failed to start child: MyApp.Controller
    ** (EXIT) an exception was raised:
        ** (Ecto.ConstraintError) constraint error when attempting to insert struct:

    * results_result_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 defined the following constraints:

    * result (unique_constraint)

            (ecto 3.5.8) lib/ecto/repo/schema.ex:688: anonymous fn/4 in Ecto.Repo.Schema.constraints_to_errors/3
            (elixir 1.11.2) lib/enum.ex:1399: Enum."-map/2-lists^map/1-0-"/2
            (ecto 3.5.8) lib/ecto/repo/schema.ex:673: Ecto.Repo.Schema.constraints_to_errors/3
            (ecto 3.5.8) lib/ecto/repo/schema.ex:654: Ecto.Repo.Schema.apply/4
            (ecto 3.5.8) lib/ecto/repo/schema.ex:262: anonymous fn/15 in Ecto.Repo.Schema.do_insert/4
            (elixir 1.11.2) lib/enum.ex:786: Enum."-each/2-lists^foreach/1-0-"/2
            (myapp 0.1.0) lib/myapp/controller.ex:71: MyApp.Controller.init/1
            (stdlib 3.14.1) gen_server.erl:417: :gen_server.init_it/2
            (stdlib 3.14.1) gen_server.erl:385: :gen_server.init_it/6
            (stdlib 3.14.1) proc_lib.erl:226: :proc_lib.init_p_do_apply/3

The error very clearly says

call unique_constraint/3 on your changeset with the constraint :name as an option.

But I am already calling the unique constraint.
I have also tried adding the suggested name parameter like this:

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

But no luck.

Does anyone have an idea what is the problem here?

Check your indexes in DB and try to provide exact name. Or rollback migration and add the same name to index and to unique_constraint.

I’d dig into implementation of unique_constraint to understand what index was generated automatically.

In any case, :name parameter accepts full index name, so this should work

|> unique_constraint(:result, name: :results_result_index)
2 Likes

And you’ve created :result table, instead of :results, it shouldn’t work at all. I guess it was modified several times, so you are in a some inappropriate state, check everything in DB to determine current status.

Good point, thank you. It is a typo coming from simplification of names. It is not in the code. I’ve just edited the original post to avoid the confusion

This was indeed the solution! Thank you, I did not know about it!
I think it would be useful to add this to documentation somewhere as it is a very simple and frequently needed use case. Also postgres is out-of-the-box db for Ecto.

Anyone has an idea what would be a good place for it?

Such example is already present in Ecto documentation for Ecto.Changeset.uniq_constraint/3 function:

In complex cases, instead of relying on name inference, it may be best to set the constraint name explicitly:

# In the migration
create unique_index(:users, [:email, :company_id], name: :users_email_company_id_index)

# In the changeset function
cast(user, params, [:email])
|> unique_constraint(:email, name: :users_email_company_id_index)
1 Like

Indeed it is, now I understand what it means.
I actually read it, however, this was not clear to me as I couldn’t have known that the name in the database can differ from the name provided to Ecto.

I believe there is room for improvement.