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

Info: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屋！