Migration for changing on_delete option on references column

Question

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?

Answer 1

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