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.

Consideration

You have working answer in you question, it'be useful to post it as a separate answer so anyone can vote. – Real

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

Preoccupy

I ended up doing a variation of this. – Consideration

The drop_if_exists index(:videos, [:user_id]) line doesn't make sense since that removes a normal index, not a foreign key constraint. The answer by Jessie is better IMO: https://mcmap.net/q/396081/-modify-foreign-key-in-ecto – Cauchy

This doesn't seem to work anymore. The solution below by Max Clarke works. – Cheung

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

Habitat

Doesn't support it with mysql as of now - (ArgumentError) MySQL adapter does not support constraints – Prolactin

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.

Decide

This should be higher – Setter

Shouldn't flush() be used ? drop constraint(: videos, "videos_user_id_fkey") should work. – Betz

Answer 4

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)

Nitaniter

Answer 5

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

Saintjust

I was hoping that there was a better way of doing it. In this particular example i could just drop the column and re-add it, but if this was a real-world situation that column would probably already contain valuable information that I would be very sorry to loose :) – Consideration

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

Tighe