Unique_constraint does not work for changeset, although properly setup in data, migrations and controller

Hi OGs,
I got a unique_constraint declared in my changeset as follow

defmodule App.Data.User do
use Ecto.schema
import Ecto.Changeset

schema "users" do
field(:full_name, :string)
field(:email, :string)
field(:username, :string)
field(:password, :string)

@required_fields []

def changeset(struct, params \\ :empty) do
  |> cast(params, @required_fields ++ @optional_fields)
  |> StringValidator.validate()
  |> unique_constraint(:email)

My migration:

defmodule App.Data.Repo.Migrations.CreateUsers do
 use Ecto.Migration
 def change do 
 create table(:users) do
      add(:full_name, :string)
      add(:email, :string)

    create(unique_index(:users, :email))

    create(index(:users, :full_name))

My controller :

def create(attrs \\ %{}, status_code \\ "approached", opts \\ [sync: true]) do
    with {:ok, user} <-
           |> user_with_status()
           |> User.changeset(attrs)
           |> Repo.insert(),

      insert_or_update_subscriber(user, opts)
      {:ok, user}

But I still can create a new user with the same email, that would make a duplicate entry
Any thoughts ?

@required_fields []

You aren’t passing :email into cast, so you are creating entries in your database that don’t have an email at all, which is why the unique_constraint is not triggering.

1 Like

I’ve also forgotten to simply pass fields into cast so many times… :exploding_head:

Hi @joseph-lozano
Thanks for the reply, I tried and it still doesn’t work,
I passed it into cast and re-ran ecto.migrations as well.

Just for clarity, is it not working in that you’re expecting to see an error returned in the changeset and not be able to submit the form? Or you’ve checked the actual database and seen that duplicate emails are being saved?

I’m not sure if I’m asking clearly, but I ask because unsafe_validate_unique/4 can be used to quickly return an invalid changeset error for UI purposes, and then you follow it up with a call to unique_constraint/3 to ensure the constraint at the database level.

Another thing to check, is that you are calling Ecto.Repo.insert/2 or Ecto.Repo.update/2 in your Users.create function from your Users.create(user_params) call.

Hi @f0rest8 ,
Yes, I checked the postgres database and I’ve seen that duplicate emails are saved there.
I also tried your suggestion that to call Ecto.Repo.Insert/2, and it did insert it with the same email.

It might be that you added the unique_index to the migration after they had already run. Have you ensured that you have rolled-back and then re-run the migrations?


Second this, it seems the problem is actually unique index is not created on DB when it should be created. That’s why DB allow duplicate email. For me, this occasionally happen when i was accidentally run ecto migration even when i haven’t finish written migration file (solution: rollback and migrate, though sometime you need to adjust the migration file to previous version first before rollback), or i just forgot to run the migration

1 Like

You’ll get a postgres unique_violation error when trying to add a unique index on a column that already contains duplicated values, and will even tell you which value it failed on:

08:42:10.891 [info] == Running 20220321064118 GenericNew.Repo.Migrations.MakeUserNameUniqueIndex.change/0 forward

08:42:10.893 [info] create index users_name_index
** (Postgrex.Error) ERROR 23505 (unique_violation) could not create unique index “users_name_index”

table: users
constraint: users_name_index

Key (name)=(test) is duplicated.

Are you sure it’s allowing duplicates when testing with non-NULL emails? Because according to psql docs PostgreSQL: Documentation: 14: 5.4. Constraints

In general, a unique constraint is violated if there is more than one row in the table where the values of all of the columns included in the constraint are equal. However, two null values are never considered equal in this comparison. That means even in the presence of a unique constraint it is possible to store duplicate rows that contain a null value in at least one of the constrained columns.

1 Like

As a starting point, do try to insert two records with identical emails from inside psql and see where that gets you.

If you can’t, make sure you have undone the migration that adds the index and then re-run it.

Sorry guys
Turn out that there was a migration file that does

def change do
   drop(unique_index(:user, :email))
   create(index(:user, :email))

Just a misunderstanding with my teammate.
Thanks guys


Never hurts to pg_dump your database structure and analyse what’s actually going on in there.

mix ecto.dump is your friend. :smile: