Ecto为Mysql/Mariadb创建唯一索引失败 [英] Ecto creating unique index failed for Mysql/Mariadb

问题描述

我尝试进行以下迁移:

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

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

还尝试了create unique_index :shoppers, [:name], name: :name_unique，create unique_index :shoppers, [:name], name: "name_unique"和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.

注意:我正在使用ecto 1.02

Note: I'm using ecto 1.02

以下是使用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

信息:name字段是utf8mb4，由我的模式指定

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

更新:我知道解决方案是减少name字段的长度，但是如何使其与phoenix模型和迁移一起使用?如预期的那样?

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?

推荐答案

感谢何塞·瓦利姆(JoséValim)帮助我完成了他的回答，尽管此回答正是解决我问题的准确方法.

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

使用以下代码创建新的ecto迁移脚本:

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

这篇关于Ecto为Mysql/Mariadb创建唯一索引失败的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！