Ecto: `references` does not work with `alter` `table` at least with PostgreSQL

Created on 29 Jun 2015 · 13Comments · Source: elixir-ecto/ecto

OS: OS X 10.10.3
Ecto: v0.13.0
PostgreSQL: 9.4.4

Currently migration like:

    alter table(:sale_deployments) do
      modify :sale_id, references(:sales, on_delete: :delete_all, type: :serial)
    end

will generate following SQL statement:
ALTER TABLE "sale_deployments" ALTER COLUMN "sale_id" TYPE integer REFERENCES "sales"("id") ON DELETE CASCADE
wich is wrong according to pg's docs: http://www.postgresql.org/docs/9.1/static/sql-altertable.html

Correct expression should be something like: ALTER TABLE "sale_deployments" ADD CONSTRAINT "sale_deployments_FK" FOREIGN KEY (sale_id) REFERENCES "sales" ("id") ON DELETE CASCADE; Another way to deal with it may be to prohibit usage of references inside alter block and suggest using plaing SQL.

Source

sumerman

Most helpful comment

To fix this problem I used execute to remove fkey.

defmodule Rumbl.Repo.Migrations.ChangeUserIdFromVideos do
  use Ecto.Migration

  def up do
    execute "ALTER TABLE videos DROP CONSTRAINT videos_user_id_fkey"

    alter table(:videos) do
      modify :user_id, references(:users, on_delete: :delete_all)
    end
  end

  def down do
    execute "ALTER TABLE videos DROP CONSTRAINT videos_user_id_fkey"

    alter table(:videos) do
      modify :user_id, references(:users, on_delete: :nothing)
    end
  end
end

And works! (:

spalenza on 17 Dec 2016

👍5

All 13 comments

I guess execute/1 should be currently used for such queries

chvanikoff on 29 Jun 2015

That's exactly what I ended up doing. But I spent some time before I figured out what was happening. So compile-time check or at very least a line in the documentation should mention this.

sumerman on 29 Jun 2015

It is a bug, we should figure out how to support it. :)

josevalim on 29 Jun 2015

Yes, and once we do this we should add tests for those features in the integration suite.

josevalim on 4 Jul 2015

@hubertlepicki this is also in the adapter area you are a bit familiar with. Would you like to give this a try? :)

josevalim on 12 Jul 2015

sure happy to have a look. You know, @josevalim, one day I'll actually need to use this library

hubertlepicki on 12 Jul 2015

This is not yet done (so not a PR yet) but the implementation for postgres will be along those lines: https://github.com/amberbit/ecto/commit/c0d059319e1187757af5a39e6734edac0f8f6a5f

I need to work further on MySQL and test if it actually works agains real database backend, not just concatinating and comparing strings ;)

hubertlepicki on 13 Jul 2015

Awesome! Integration tests can be added here: https://github.com/elixir-lang/ecto/blob/master/integration_test/sql/migration.exs

josevalim on 13 Jul 2015

Hello, this problem is fixed? Ecto return this error when I try modify table with references.

18:32:30.821 [info]  alter table videos
** (Postgrex.Error) ERROR (duplicate_object): constraint "videos_user_id_fkey" for relation "videos" already exists

ecto 2.0.6
postgres (PostgreSQL) 9.6.1

def up do
  alter table(:videos) do
    modify :user_id, references(:users, on_delete: :delete_all)
  end
end

Thanks.

spalenza on 10 Dec 2016

@rodolfospalenza Might be better to ask on the mailing list or forums, however I'm thinking that your :user_id column already has a foreign reference on it and you are trying to change the :on_delete functionality? If so this might sound worth opening a new PR for. Should show the prior migration for the same table though too.

OvermindDL1 on 16 Dec 2016

Hello @OvermindDL1,

Thank for your answer. I will try IRC elixir channel.
The migration to create videos table is this.

defmodule Rumbl.Repo.Migrations.CreateVideo do
  use Ecto.Migration

  def change do
    create table(:videos) do
      add :url, :string
      add :title, :string
      add :description, :text
      add :user_id, references(:users, on_delete: :nothing)

      timestamps()
    end
    create index(:videos, [:user_id])

  end
end

spalenza on 17 Dec 2016

To fix this problem I used execute to remove fkey.

defmodule Rumbl.Repo.Migrations.ChangeUserIdFromVideos do
  use Ecto.Migration

  def up do
    execute "ALTER TABLE videos DROP CONSTRAINT videos_user_id_fkey"

    alter table(:videos) do
      modify :user_id, references(:users, on_delete: :delete_all)
    end
  end

  def down do
    execute "ALTER TABLE videos DROP CONSTRAINT videos_user_id_fkey"

    alter table(:videos) do
      modify :user_id, references(:users, on_delete: :nothing)
    end
  end
end

And works! (:

spalenza on 17 Dec 2016

👍5

The current solution to this is similar, but you don't need to drop to raw SQL. The above example can be written as

defmodule Rumbl.Repo.Migrations.ChangeUserIdFromVideos do
  use Ecto.Migration

  def up do
    drop constraint("videos", "videos_user_id_fkey")

    alter table(:videos) do
      modify :user_id, references(:users, on_delete: :delete_all)
    end
  end

  def down do
    drop constraint("videos", "videos_user_id_fkey")

    alter table(:videos) do
      modify :user_id, references(:users, on_delete: :nothing)
    end
  end
end