Delete nested associations

Questions / Help
1

Hello everyone,

I have a problem to delete nested has_many relations.

Indeed, in this example below, when i delete a User with Comments (and Comments containing posts), i have a database foreign key violation error. Because i have set a foreign_key "comment_id " in my Posts table to protect it.

Is there a solution to use a cascade method?

Thanks.

defmodule User do
use Ecto.Schema
import Ecto.Changeset

schema “users” do
field :name
field :email
has_many :comments, on_delete: :delete_all
end

end

defmodule Comment do
use Ecto.Schema
import Ecto.Changeset

schema “comments” do
field :txt
has_many :posts, on_delete: :delete_all
end

end

2

Can you show how you created the tables? Your migration files?

3

Directly in PostgreSQL !

4

Well, I have the following migrations:

defmodule Test.Repo.Migrations.AddStuff do
  use Ecto.Migration

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

    create table(:posts) do
      add :body, :text
      add :user_id, references(:users, on_delete: :delete_all)
    end

    create table(:comments) do
      add :body, :string
      add :post_id, references(:posts, on_delete: :delete_all)
    end
  end
end

and schemas

defmodule Test.User do
  use Ecto.Schema

  schema "users" do
    field :name, :string
    has_many :posts, Test.Post
  end
end

defmodule Test.Post do
  use Ecto.Schema

  schema "posts" do
    field :body, :string
    has_many :comments, Test.Comment
    belongs_to :user, Test.User
  end
end

defmodule Test.Comment do
  use Ecto.Schema

  schema "comments" do
    field :body, :string
    belongs_to :post, Test.Post
  end
end

and testing this in iex

iex(1)> {:ok, user} = Test.Repo.insert(%Test.User{name: "asdfg"})
{:ok, %Test.User{id: 1, ...}}

iex(2)> {:ok, post} = user |> Ecto.build_assoc(:posts, %{body: "asd"}) |> Test.Repo.insert()
{:ok, %Test.Post{id: 1, ...}}

iex(3)> {:ok, comment} = post |> Ecto.build_assoc(:comments, %{body: "asd"}) |> Test.Repo.insert()
{:ok, %Test.Comment{id: 1, ...}}

iex(4)> Test.Repo.delete(user)
{:ok, %Test.User{id: 1, ...}}

works as expected.

You might want to add ON DELETE CASCADE to how your create you tables in postgres, (which is what references(table, on_delete: :delete_all) does in the migrations above). See https://stackoverflow.com/questions/33939169/elixir-delete-many-to-many-association for a very similar question.

7 Likes
5

The docs for Schema states that you need to enable CASCADE delete on the table for this to work:

:on_delete - The action taken on associations when parent record 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 preferred. :nilify_all and :delete_all will not cascade to child records unless set via database migrations.

So follow the great answer by @idi527 to make it work.

3 Likes
6

Oh yeah nice guys ! Thanks ! That works !
The problem came from database: CASCADE delete not enable!

7

Sounds interesting.

For those like me who have already built and played migrations, without the :on_delete option, could someone help writing the change migration file to get it afterwards?

Thanks a lot!

8

This is what I have used:

You’ll need to drop the fk constraint first:

drop(constraint(:my_table, :my_table_thing_id_fkey))

Then you can set the on_delete config as required:

alter table(:my_table) do
modify(:thing_id, references(:things, on_delete: :delete_all))
end

Not 100% that this is the best way to do it but it works for me.

5 Likes
9

Since this thread is on the first page of results when searching this topic, I’d like to add that one can create reversible migrations.

For example. to go between :nothing (default) and :delete_all:

alter table(:my_table) do
  modify(:thing_id, references(:things, on_delete: :delete_all),
    from: references(:things, on_delete: :nothing))
end
9 Likes