22

I have 2 tables:

user:

id
username
password

unique_index username

(the schema has a has_many other)

other:

id
user_id - references(:users)
foo

index user_id

(the schema has a belongs_to user)

in the changeset for "Other" i have this

model
|> cast(params, @req, @opt)
|> foreign_key_constraint(:user_id)

My assumption at this point was the "Other" ecto model requires a "User" to be associated with it to exist (which is what I want)

But my second assumption was if I delete the "User" record then all associated "Other" records would be deleted (via a Cascade delete)

What actually happens is I have a Ecto.ConstraintError when trying to delete a "User" record (I'm assuming because there is an "Other" record associated to that User)

So how would I go about having it work in the way I want which is:

  • A "user" can be created standalone
  • An "other" can be created but must belong to a "user"
  • When an "other" is deleted it doesn't affect anything else
  • When a "user" is deleted it deletes all associated "other" records too

Essentially a Cascade Delete on the User for any items that references it

TheStoneFox
  • 3,007
  • 3
  • 31
  • 47

2 Answers2

51

You can do it the way you specified on your schema using:

has_many :other, Project.Other, on_delete: :delete_all

However, you may be better doing this in your migration with references/2:

create table(:others) do
  add :user_id, references(:users, on_delete: :delete_all)
end

This will use database foreign key constraints and is mentioned in the has_many docs:

:on_delete - The action taken on associations when parent model is deleted. May be :nothing (default), :nilify_all and :delete_all. Notice :on_delete may also be set in migrations when creating a reference. If supported, relying on the database via migrations is prefered

You can change an existing index with:

drop_if_exists index(:others, [:user_id])
alter table(:others) do
  modify :user_id, references(:users, type: :uuid, on_delete: :delete_all)
end
Gazler
  • 83,029
  • 18
  • 279
  • 245
  • The solution for an existing index doesn't seem to work as of now (June 2018). This solution here works: https://stackoverflow.com/a/45770507/1460448 – xji Jun 12 '18 at 14:26
7

I figured it out, on the has many you can pass an on_delete option

so the has_many looks like this

has_many :other, Project.Other, on_delete: :delete_all

Docs are pretty helpful :p

https://hexdocs.pm/ecto/Ecto.Schema.html#has_many/3

TheStoneFox
  • 3,007
  • 3
  • 31
  • 47