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)
end
@required_fields []
@optional_fields[
:fullname,
:email,
:username,
:password]
def changeset(struct, params \\ :empty) do
struct
|> cast(params, @required_fields ++ @optional_fields)
|> StringValidator.validate()
|>validate_required(@required_fields)
|> unique_constraint(:email)
end
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)
end
create(unique_index(:users, :email))
create(index(:users, :full_name))
end
end
My controller :
def create(attrs \\ %{}, status_code \\ "approached", opts \\ [sync: true]) do
with {:ok, user} <-
status_code
|> user_with_status()
|> User.changeset(attrs)
|> Repo.insert(),
insert_or_update_subscriber(user, opts)
{:ok, user}
end
end
But I still can create a new user with the same email, that would make a duplicate entry
Any thoughts ?
Thanks
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.
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
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:
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.
