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?
1 Like
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?
).
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
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
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
13 Likes