ardhitama ardhitama - 6 months ago 37
MySQL Question

Ecto creating unique index failed for Mysql/Mariadb

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

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