40

How to make a field unique in ecto?

I thought it's the same as the active record in Ruby, but it seems it isn't

ardhitama
  • 1,949
  • 2
  • 18
  • 29

1 Answers1

76

You want to use unique_constraint/3. This is not like Active Record because it is using the database to ensure uniqueness. Active Record would do a query for records with the same value and if any were returned then it would fail. This has a race condition where, if a value is inserted between fetching to check uniqueness and inserting your record, you will either end up with duplicate data or an error being raised (depending on if an index is set on the database or not. unique_constraint/3 does not have this race condition.

One thing worth noting is that since the uniqueness is not known until an insert is attempted, the unique constraint will happen after your validations. It is not possible to display both validation and constraint errors at once.

The database you are using must support unique constraints too. They won't work with SQLite. You can read more on the [GitHub issue]https://github.com/elixir-lang/ecto/issues/865).

When using a SQLite3 database all you need is to use the ecto3_sqlite3 adapter, and the unique constrain will work as expected.

Add to your mix.exs:

{:ecto_sqlite3, "~> 0.7.7"},

In your migration:

create unique_index(:users, [:email])

Then in your model:

cast(user, params, ~w(email), ~w())
|> unique_constraint(:email)

It is worth noting that Ecto used to provide a validate_unique/3 function which worked by doing a query on the database, however it was deprecated in favour of unique_constraint/3 In version 0.16.0

Exadra37
  • 11,244
  • 3
  • 43
  • 57
Gazler
  • 83,029
  • 18
  • 279
  • 245
  • Hi, it leads to another error http://stackoverflow.com/questions/32468172/ecto-creating-unique-index-failed – ardhitama Sep 08 '15 at 22:20
  • 1
    What does the ~w(email) and ~w() do? – sheldonkreger Aug 30 '16 at 03:21
  • The ~w(...) syntax creates a list. http://elixir-lang.org/docs/stable/elixir/Kernel.html#sigil_w/2 – Gazler Aug 31 '16 at 12:33
  • sigil link in the comment above is broken. here is one that works: https://elixir-lang.org/getting-started/sigils.html#word-lists – Sgnl Jul 13 '18 at 23:23
  • "It is not possible to display both validation and constraint errors at once." Ecto provides `unsafe_validate_unique` to approximate this. It's to "provide quick feedback to users", so it might sometimes make sense to implement both. – atomkirk Aug 28 '20 at 02:43
  • I thought it would be good to have a link for relevant `uniqueness` documentation for Ruby as well: https://guides.rubyonrails.org/active_record_validations.html#uniqueness – Burak Kaymakci Jul 05 '21 at 15:11