Delete child when parent deleted

Questions / Help
#1

I have a sales territories table that has a many to many relationship with a states table. I join through a territories_states table. I want the records with a certain territory id to be deleted from territories_states when I delete from territories. Right now when I run the below delete function it deletes the record from the territories table but not from the territories states table despite adding on_delete: :delete_all to the migration (I didn’t create a new migration for this I just added the on_delete: :delete_all to the original territories_states table)

Here is my delete function:

  def delete(id) do
      territory = Map.get(id, "id")
      |> IO.inspect()
  
      __MODULE__
      |> where([t], t.id == ^territory)
      |> Repo.delete_all()
    end

Here is my migration for the territories states table:

defmodule Gimli.Repo.Migrations.CreateTerritoriesStatesTable do
  use Ecto.Migration

  def change do
    create table(:territories_states) do
      add :state_id, references(:states)
      add :territory_id, references(:territories, on_delete: :delete_all)
    end

    create unique_index(:territories_states, [:state_id, :territory_id])
  end
end

Here is my schema for the territories table:

schema "territories" do
    field(:territory_name, :string)
    field(:ownerid, {:array, :string})
    

    timestamps()
    
    belongs_to(:sub_verticals, SubVertical, foreign_key: :sub_vertical_id, on_replace: :delete)  
    has_many(:users, User)
    many_to_many(:states, State, join_through: "territories_states", on_replace: :delete, on_delete: :delete_all)  
  end

here is a newer territories_states migration:



defmodule Gimli.Repo.Migrations.AlterUniqueIndexTerritoriesTable do
  use Ecto.Migration

  def change do
    execute "ALTER TABLE territories_states DROP CONSTRAINT territories_states_territory_id_fkey"
    create_if_not_exists index(:territories_states, [:territory_id]) 
      
    
  end
end

I’m not sure what I’m missing and am under the impression that I have the on_deletes correct. Any help appreciated!

#3

Why did you dropped the foreign key constraint in the join table?

#4

Hi @sfusato. Thanks for responding. I dropped it to recreate it with on_delete: :delete_all. I mistakenly pasted an old version of the migration. Below is what the migration looks like now. It wasn’t allowing me to delete the record in territories and so I ran a migration that deleted the old territory_id foreign key and recreated it with on_delete: :delete_all in it to see if that would work.

  use Ecto.Migration

  def change do
    execute "ALTER TABLE territories_states DROP CONSTRAINT territories_states_territory_id_fkey"
    create_if_not_exists index(:territories_states, [:territory_id, on_delete: :delete_all]) 
      
    
  end
end
#5

Which means it worked as it should. But, note that an index is not the same thing as a foreign key constraint. Instead, you should have modified the field like this to add the on_delete: :delete_all option:

drop constraint("territories_states", "territories_states_territory_id_fkey")

alter table(:territories_states) do
 modify :territory_id, references(:territories, on_delete: :delete_all)
end
2 Likes
#6

Thank you! That worked! So for future reference I just need to make a distinction between setting an index and setting a constraint on that index. right?