Modify foreign key in Ecto

Question

I have this original migration that have already been run and sent upstream:

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])

Now i wish to change the foreign key on user_id to cascade deletions, so that when a user is deleted all of his associated videos will also be deleted.

I have tried the following migration:

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

But this raises an error:

(Postgrex.Error) ERROR (duplicate_object): constraint "videos_user_id_fkey" for relation "videos" already exists

How can I formulate a migration script that will change this foreign key according to my requirement?

---

UPDATE

I ended up with the following solution:

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

this drops the constraint before ecto tries to recreate it.

Answer 1

You can drop the index before calling alter:

drop_if_exists index(:videos, [:user_id])
alter table(:videos) do
  modify :user_id, references(:users, on_delete: :delete_all)
end

Doing the opposite is a little trickier:

execute "ALTER TABLE videos DROP CONSTRAINT videos_user_id_fkey"
create_if_not_exists index(:videos, [:user_id])

Answer 2

I'm not sure when this was added to Ecto, but at least in 2.1.6 there's no need for raw SQL anymore. drop/1 now supports constraints (drop_if_exists/1 doesn't though):

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

Answer 3

I ended up with the following solution:

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

this drops the constraint before ecto tries to recreate it

Copied from the question.

Answer 4

I don't think that it can achieved with alter table. For example according to this answer Postgres doesn't allow modifying constraints in ALTER TABLE statement. MySQL also doesn't allow modifying constraints.

The easiest thing to do would be removing the field and adding it back if you don't have any data. Otherwise, you need use raw SQL with execute

Answer 5

In Ecto SQL 3.4.3:

"If the :from value is a %Reference{}, the adapter will try to drop the corresponding foreign key constraints before modifying the type."

modify :user_id, references(:users, on_delete: :delete_all), from: references(:users)

Should work. In working on a rollback, I worked out that this worked to clear the FK and remove the column:

remove :user_id, references(:users)

Answer 6

from option was introduced in Ecto 3.0, so you can setup your migration in more elegant way:

defmodule App.Migrations.ChangeVideosUserConstraint do
  use Ecto.Migration
  def change do
    alter table(:videos) do
      modify(:user_id, references(:users, on_delete: :delete_all), from: references(:users))
    end
  end
end