Adding a unique index to existing table

When following an exercise I had to add a unique index to the existing table posts.
So I generated a migration and put the following code inside:

defmodule Blog.Repo.Migrations.AddUniqueConstraintOnPostsTitle do
  use Ecto.Migration

  def change do
    alter table(:posts) do
      create unique_index(:posts, [:title])
    end
  end
end

I also updated the Post schema module by adding the constraint to the changeset/2 function:

  @doc false
  def changeset(post, attrs) do
    post
    |> cast(attrs, [:title, :content, :published_on, :visible])
    |> validate_required([:title, :content])
    |> unique_constraint(:title)
  end

Now when I run the migration with mix ecto.migrate, it fails with a very unclear error message:

 blog git:(visibility_migration) ✗ mix ecto.migrate
Compiling 1 file (.ex)

16:38:58.664 [info] == Running 20230713142708 Blog.Repo.Migrations.AddUniqueConstraintOnPostsTitle.change/0 forward
** (Ecto.MigrationError) cannot execute nested commands
    (ecto_sql 3.10.1) lib/ecto/migration/runner.ex:140: Ecto.Migration.Runner.execute/1
    (ecto_sql 3.10.1) lib/ecto/migration.ex:551: Ecto.Migration.create/1
    priv/repo/migrations/20230713142708_add_unique_constraint_on_posts_title.exs:6: Blog.Repo.Migrations.AddUniqueConstraintOnPostsTitle.change/0
    (ecto_sql 3.10.1) lib/ecto/migration/runner.ex:289: Ecto.Migration.Runner.perform_operation/3
    (stdlib 4.2) timer.erl:235: :timer.tc/1
    (ecto_sql 3.10.1) lib/ecto/migration/runner.ex:25: Ecto.Migration.Runner.run/8
    (ecto_sql 3.10.1) lib/ecto/migrator.ex:362: Ecto.Migrator.attempt/8
    (ecto_sql 3.10.1) lib/ecto/migrator.ex:283: anonymous fn/5 in Ecto.Migrator.do_up/5

What am I missing here?

The create index statement should be on its own, not inside an alter table.

2 Likes

Thanks a lot, I learned one more important thing today :slight_smile: