Deleting in a Many to Many Relationship

Questions / Help
,
1

ive a many to many where deleting a record is giving problems.

ill write models first then schemas:

A User belongs_to an organization and has many to many with Usergroup via a join schema Groupuser

schema "users" do

belongs_to :organization, Organization, foreign_key: :organization_id
    
    many_to_many :usergroups, Usergroup, join_through: Groupuser

Groupuser

@primary_key false
  schema "groupusers" do
    belongs_to :user, User
    belongs_to :usergroup, Usergroup

    timestamps
  end

  @required_fields ~w(user_id usergroup_id)
  @optional_fields ~w()

  @doc """
  Creates a changeset based on the `model` and `params`.

  If no params are provided, an invalid changeset is returned
  with no validation performed.
  """
  def changeset(model, params \\ %{}) do
    model
    |> cast(params, @required_fields, @optional_fields)
    |> validate_required(@required_fields)
  end

The Usergroup has many to many with User

schema "usergroups" do
    
    many_to_many :users, User, join_through: Groupuser

The Organization has_many users:

has_many :users, User, on_delete: :delete_all

The migrations are:

User

def change do
    execute("CREATE EXTENSION IF NOT EXISTS citext;")
    create table(:users) do
      add :organization_id, references(:organizations, on_delete: :delete_all)

Usergroup

def change do
    create table(:usergroups) do
      timestamps

Groupuser

def change do
    create table(:groupusers) do
      add :user_id, references(:users, on_delete: :delete_all, on_replace: :delete)
      add :usergroup_id, references(:usergroups, on_delete: :delete_all, on_replace: :delete)

      timestamps
    end
    create index(:groupusers, [:user_id])
    create index(:groupusers, [:usergroup_id])

  end
end

Upon loading an Organization entry |> Repo.preload(all relationship stuff)
and then doing Repo.delete!(Organization)
yields:

 ** (Postgrex.Error) ERROR (foreign_key_violation): update or delete on table "users" violates foreign key constraint "groupusers_user_id_fkey" on table "groupusers"
     
         table: groupusers
         constraint: groupusers_user_id_fkey
     
     Key (id)=(601) is still referenced from table "groupusers".

ive tried different ways but the error persists

3 Likes
2

Maybe you like me tried this behavior at tests and forgot to apply this migration to test database :slight_smile:

on_delete: :delete_all this must be enough to fix the error

3

@Apelsinka223 So have I not already added this line?

add :organization_id, references(:organizations, on_delete: :delete_all)

It’s there right?

4

you have this line, I suggested that you forgot to apply this migration to test database if this error raised in test (MIX_ENV=test mix ecto.migrate)

5

Should the on_delete: :delete_all (setup in the model) work even nothing is declare on the migration scheme ?

6

Yes, it would work, but not for deep associations, like when you have
User > Posts > Comments and when user deleted, posts will be deleted too, but not comments

1 Like