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

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 2

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