Ecto creating unique index failed for Mysql/Mariadb

Question

I try to do the following migration:

defmodule Shopper.Repo.Migrations.MakeNameUniqueShopper do
  use Ecto.Migration

  def change do
    create unique_index :shoppers, [:name]
  end
end

Also tried create unique_index :shoppers, [:name], name: :name_unique, create unique_index :shoppers, [:name], name: "name_unique", and create index(:shoppers, [:name], unique: true)

But they failed with similar error:

[info]  == Running Shopper.Repo.Migrations.MakeNameUniqueShopper.change/0 forward

[info]  create index shoppers_name_index
** (Mariaex.Error) (1071): Specified key was too long; max key length is 767 bytes
    (ecto) lib/ecto/adapters/sql.ex:172: Ecto.Adapters.SQL.query!/5
    (elixir) lib/enum.ex:1261: Enum."-reduce/3-lists^foldl/2-0-"/3
...
...

Any help would be very appreciated, to help me with the error.

Note: I'm using ecto 1.02

Following is the first migration created with mix phoenix.gen.model

defmodule Shopper.Repo.Migrations.CreateV1.Shopper do
  use Ecto.Migration

  def change do
    create table(:shoppers) do
      add :name, :string
      add :oauth_token, :string

      timestamps
    end
  end
end

Info: the name field is utf8mb4, specified by my schema

Update: I know the solution is to reduce the name field length, but how to make it work with phoenix model and migration? As it expects a string?

Answer 1

The field "name" is too long. You should either ensure it has size less than 767 bytes by passing the size option when declaring it or index just part of the field:

create unique_index :shoppers, ["name(20)"], name: :shoppers_name_unique

Keep in mind that you will need to give the same name when calling unique_constraint/2 in your changeset.

Answer 2

Thanks to José Valim for helping me through his answer, though this answer is the exact solution for my problem.

Create a new ecto migration script with the following code:

defmodule Shopper.Repo.Migrations.MakeNameUniqueShopper do
  use Ecto.Migration

  def change do
    alter table(:shoppers) do
      modify :name, :string, size: 100
    end

    create unique_index :shoppers, [:name], name: :shopper_name_unique
  end
end