How can I modify the on_delete behaviour of a fk reference in a migration

I created a table with a foreign key, and specified on_delete: :nothing like so:

create table(:foo) do
  add :bar_id, references(:bar, on_delete: :nothing)

  timestamps()
end

I now want to change the on_delete behaviour to be :delete_all. I tried doing alter table + modify :bar_id… in a new migration, but it gave me an “already exists” error.

Any ideas?

Can you show us your exact migration and the error it spits out?
Also I know you probably want to have this set on the DB level, but you could also configure it at the app level in the schema.

I think you need to drop the constraint first.

drop constraint(:foo, "foo_bar_id_fkey")
alter table(:foo) do
  modify :bar_id, references(:bar, on_delete: :delete_all)
end

Sure, so my migration looks like:

def change do
  alter table(:foo) do
    modify :bar_id, references(:variables, on_delete: :delete_all)
  end
end

and the exact error I get is:

2022-07-29 15:15:31.685 AEST [65481] ERROR:  constraint "foo_bar_id_fkey" for relation "foo" already exists

Also I know you probably want to have this set on the DB level, but you could also configure it at the app level in the schema.

Yep, that’s my backup plan. But I agree with you; it does feel like handling the constraint at the DB level is a better solution.

yea so you would probably want to drop the fk. you will need to set up/down however because ecto wont know how to rollback. So

def up do
  drop constraint(:foo, "foo_bar_id_fkey")

  alter table(:foo) do
    modify :bar_id, references(:bar, on_delete: :delete_all)
  end
end

def down do
  drop constraint(:foo, "foo_bar_id_fkey")

  alter table(:foo) do
    modify :bar_id, references(:bar, on_delete: :nothing)
  end
end

I am surprised this does not work (unless it does?:thinking:).
This would save you writing up and down

def change do
  drop constraint(:foo, "foo_bar_id_fkey")

  alter table(:foo) do
    modify :bar_id, references(:bar, on_delete: :delete_all), from: references(:bar, on_delete: :nothing)
  end
end