Ecto delete referenced database record

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

Solution

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