Unique_constraint can't dentify uniq multi fields in Ecto

Hello, I need to index 3 field in my database and those should be uniq, then I created my migration like this:

def change do
  create table(:user, primary_key: false) do
    add :id, :uuid, primary_key: true
    ...
    add :username, :string, size: 20, null: false, unique: true
    add :email, :string, null: false, unique: true
    ...
    add :verified_email, :string, size: 120, null: true, unique: true

    timestamps()
  end
  create(
    index(:user, [:email, :verified_email, :username],
      concurrently: true,
      name: :index_of_users,
      unique: true
    )
  )
end

and my changeset:

def changeset(struct, params \\ %{}) do
  struct
  ....
  |> unique_constraint(:username, name: :index_of_users, message: "userNAME")
  |> unique_constraint(:email, name: :index_of_users, message: "Email")
end

but my changeset can’t identify uniq fields and lets user to add whatever they want, for example, I can add 3 records with Identical emails.

You made an index which says the combination of those 3 columns must be unique, not each individual column must be unique.

6 Likes

then I should create 3 index? like this?

    create(
      index(:user, [:email],
        concurrently: true,
        name: :index_of_users_email,
        unique: true
      )
    )
    create(
      index(:user, [:verified_email],
        concurrently: true,
        name: :index_of_users_verified_email,
        unique: true
      )
    )
    create(
      index(:user, [:username],
        concurrently: true,
        name: :index_of_users_username,
        unique: true
      )
    )
1 Like

yes :slight_smile:

2 Likes

You do not need to create index concurrently there, it is different from what you probably think it does.

2 Likes

hello, I need to index my fields because I search these field many times and need to load fast, { id, email, username}, is my way false? would you mind explaining me? and is there a way to identify uniq field without creating index?

1 Like

I’m not sure what @hauleth thinks you’re thinkingbut adding indexes concurrently is usually an ok option, albeit not needed when your tables have few rows (as it’s so fast then that locking doesn’t really hurt).

If you’re using Postgres the docs are here - there’s a whole section with caveats though.

3 Likes

I meant that you do not need concurrently: true, not that you do not need indices at all.

2 Likes