How to cascade delete a belongs_to relationship?

Questions / Help
1

I have two schemas: User and Address. User has an :address_id key. Address has nothing pointing to the parent User.

How can I delete an Address when the User is deleted?

delete_all on the schema does what I want, but is discouraged in the docs

schema "users" do
  belongs_to(:address, Address, on_delete: :delete_all)
end

delete_all in the migration does the opposite of what I want: it deletes User when Address is deleted:

alter table(:users) do
  add(:address_id, references(:addresses, on_delete: :delete_all))
end

I think what I want in SQL is this, but I’m not sure how to do it in Ecto:

ALTER TABLE "User"
FOREIGN KEY (address_id)
REFERENCES Address(address_id)
ON DELETE CASCADE;

Apologies for such a basic question. I’m sure there are already answers about this but I’m struggling to find them.

1 Like
2

This is kind of bending my mind because the answer in the linked question seems backward to me but since it’s Matt saying it I feel I must be reading it wrong. I use on_delete: :delete_all often enough and it works like you were thinking it should in the other thread.

I tested for my own sanity:

  def change do
    create table(:users) do
      add :name, :text
    end

    create table(:addresses) do
      add :address, :text
      add :user_id, references(:users, on_delete: :delete_all)
    end
  end

Then in psql:

psql=# insert into users (name) values ('One'), ('Two');
psql=# insert into addresses (address, user_id) values ('Addr One', 1), ('Addr Two', 2);
psql=# select * from users;
 id |   name
----+----------
  1 | One
  2 | Two

psql=# select * from addresses;
 id | address  | user_id
----+----------+---------
  1 | Addr One |       1
  2 | Addr Two |       2

psql=# delete from addresses where id = 2;
psql=# select * from users;
 id |   name
----+----------
  1 | One
  2 | Two

psql=# delete from users where id = 1;
psq=# select * from addresses
 id | address | user_id
----+---------+---------
(0 rows)

It’s totally possible I’m missing something or read something wrong? I dunno, going a bit batty!

Once it does work in SQL, you don’t have to do anything to make it work in Ecto, the rows will just get deleted. Just to add my 2 cents (which you are of course welcome to leave on the table), I would recommend against relying on this behaviour, though. You absolutely should have it for data integrity, but even if it feels a bit weird, I would run an Ecto.Multi that explicitly deletes all the relationships. This way the behaviour is explicitly spelled out in the business logic and not hidden away in the database. In the case of user, people probably expect that a bunch of other stuff is being deleted as well, but they won’t know what without doing some digging.

1 Like
3

Yeah, that’s what really has me tripped up. I’m wondering if it’s because most people would add the reference the other way?

EG a has_one on the Address, so address.user_id references the record to be deleted with? In my case I want Address to be used with a variety of parents, so I don’t want the ID setup that way.

I think the multi is a good idea. It would certainly make the behavior a lot more explicit too than having to dig through past migrations.

4

Oh ya ok, yeesh, I spent more time looking at your linked thread and less at your example above! My brain clearly switched around users and address_id up there (as happens to me often). Oh boy, sorry about that.

If address can belong to different parents then I’m actually not sure postgres has something like that out of the box. If an address can only have one parent then my brain is clearly not working super well today so I don’t know the answer right now :sweat_smile: If addresses are denormalized then this is one of those cases where I just go for an embedded schema due to simplicity. There was some interesting discussion on that recently which might make me reconsider but probably not :slight_smile:

Anyway, sorry I couldn’t be of more help!

5

Oops, I meant to say that an Address can have different types of parents, so I don’t want multiple nullable refs back to the various kinds of parents (eg address.user_id, address.store_id, address.home_id… But an Address will always only have one parent, so if that parent with an :address_id is deleted, the Address should be too.

You’ve been a big help, thanks! I’ll go with the multi and hope there’s an additional SQL/migration setting I’m missing as a fallback like you suggested.

1 Like
6

Ya, it seems I’m not crazy and cascading from child to parent is not a thing (at least not according to, for example, this).

I realize that if you use a Multi, you’re good to go so long as you have an fkey constraint set up. While it won’t cascade, the database will make it impossible to delete a user without first deleting the address, so you’d be forced to deal with it manually anyway.

7

I think from that link you shared it’s highlighting how most people would do this: have the child reference back to it’s parent. In Ecto this association is even called belongs_to.

when the parent (referenced) record is deleted, any child (referencing) records are also deleted

In my case my parent is referencing the child, so the association is reversed.

8

Yes, it’s in answer to a question asking if it’s possible to do it the other way!

1 Like