Unique field with postgres and ecto

Questions / Help
1

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?

2

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.

3

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)
3 Likes
4

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.

5

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

6

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?

7

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
8

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.