Migration for changing on_delete option on references column

ecto, elixir, phoenix-framework

Solution

You can use `modify` for this, passing the new `on_delete` options in the new `type`, but I encountered this bug while trying this, the fix for which is to manually `DROP` the constraint first. You'll also need to specify both an `up` and a `down` version since `modify` is not reversible.

def up do
  execute "ALTER TABLE posts DROP CONSTRAINT posts_user_id_fkey"
  alter table(:posts) do
    modify(:user_id, references(:users, on_delete: :delete_all))
  end
end

def down do
  execute "ALTER TABLE posts DROP CONSTRAINT posts_user_id_fkey"
  alter table(:posts) do
    modify(:user_id, references(:users, on_delete: :nothing))
  end
end

Problem

I was playing with Phoenix and making has_many associations. I usually do `on_delete: :delete_all` as an option to the reference column. But if I change my mind and want to change it later for `nilify_all`, is there a way to this with inside a migration? Migration for creating the table: ``` def change do create table(:messages) do add :body, :text add :sender_id, references(:users, on_delete: :delete_all) timestamps() end create index(:messages, [:sender_id]) end ``` I'm looking for something like this: ``` def change do change_options table(:messages), :user_id, on_delete: :nilify_all end ``` I've seen modify and alter but I didn't see anything about on_delete. Maybe it's not possible via migrations?

Original source