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

Phoenix Forum Questions / Help
1

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?

2

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.

3

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
4

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.

5

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
3 Likes
6

As of Ecto 3.10.2, you’ll need to do the up/down method above!

I was curious about this too so I tried the change method, but got
** (Postgrex.Error) ERROR 42704 (undefined_object) constraint "foo_bar_id_fkey" of relation "foo" does not exist

7

For anyone coming here via Google, something like this:

alter table("comments") do
  modify :post_id, references(:posts, on_delete: :delete_all),
    from: references(:posts, on_delete: :nothing)
end

Now works as per example here in docs. Not sure exactly when this changed but hopefully saves someone some time. Tested with Ecto 3.10.3

5 Likes