Need mentoring: many-to-many tables to prevent Ecto Constraint Error

I have a number of many-to-many tables. I wrote these when I was first starting out and just followed examples. I need to clean up these tables and I want to do it right. I have some questions below and would really appreciate mentoring from experts in this area so that I do this right because I keep getting Ecto Constraint Errors when I do deletions.

I’ll use the easiest example which is a Resource that has Topics. A resource can have many topics and a topic can be associated with many resources.


def change do
    create table(:join_resources_topics, primary_key: false) do
      add :resource_id, references(:resources, on_delete: :delete_all), on_update: :nothing, primary_key: true
      add :topic_id, references(:topics, on_delete: :delete_all, on_update: :nothing), primary_key: true

    create unique_index(:join_resources_topics, [:resource_id, :topic_id], name: resource_topic_index)


Migration Questions:

  1. :on_replace
    The Hex documents for “references” gives a list of options. There is no mention of :on_replace in this section, but I see it in other documentation. The Hex documents for Polymorphic Associations (Polymorphic associations with many to many — Ecto v3.10.3) mention an :on_replace callback as does the documentation for Ecto.Changeset (Ecto.Changeset — Ecto v3.10.3). My understanding is that :on_replace kicks in when using cast_assoc. So a resource might be associated with topic IDs (1, 2, 3). I do a cast_assoc and tell it that it’s now only associated with topic IDs (1, 2). The :on_replace tells the join table what to do with the previous association between the resource and Topic ID 3.

Should I define :on_replace in the migration’s references function? It’s not listed as an option in the documentation but I’m not sure where else I would put this.

  1. :on_delete vs :cascade
    A topic is a simple table (:name, :category). So if a topic is deleted, then :delete_all makes sense. I’ve seen :cascade as another option in a number of examples. My understanding is that I’d only use :cascade if I needed to delete children. For example, I have a table for messages and replies (children messages), If I delete a message, I obviously want to delete all of the associations for children messages. In that case, I would use on_delete: :cascade.

Is it safer to just always use :cascade instead of :delete_all even if no children are involved?


defmodule MyApp.JoinResourcesTopics.JoinResourceTopic do
  use Ecto.Schema
  import Ecto.Changeset

  @primary_key false
  schema "join_resources_topics" do
    field :topic_id, :integer, primary_key: true
    field :resource_id, :integer, primary_key: true

  @doc false
  def changeset(join_resource_topic, attrs) do
    |> cast(attrs, [:resource_id, :topic_id])
    |> validate_required([:resource_id, :topic_id])

I get this error on some of my join tables and not on others:

(Ecto.ConstraintError) constraint error when attempting to delete struct:
The changeset has not defined any constraint.

I’ve read through this Elixir Forum post("constraint error when attempting to delete struct:"), but I still don’t understand why it happens. If I remove a Topic, then the join table should just remove any join that has a resource associated with that Topic because now the Topic is gone. I know it has something to do with the ID still being in use, so I want to implement a Schema that allows the deletion to happen gracefully.

I understand from that post that I need to modify the delete function so that it uses a Changeset.

Should it look like this?

def delete_join_resource_topic(%JoinResourceTopic{} = join_resource_topic) do
   |> Changeset.change()
   |> Changeset.foreign_key_constraint( ??? what ID do I use since it’s a many-many ???, name: resource_topic_index“, message: “can’t be deleted, it is still in use.”)
   |> Repo.delete

But all that does is gracefully give an error … it still doesn’t delete the joins associated with the Topic. How do I ensure (via my migration and schema) that when a Topic is deleted, all of the Resource-Topic joins with that topic are also deleted?

Are there best practices I should follow on the schema above as it relates to many-to-many tables? Should I add anything to it so that it is safer or more efficient?

1 Like

Please provide schema definitions for resources and topics.

I am not clear what your issue is? You can always use put_assoc, that removes associations nicely – though likely will not delete the joining records themselves in which case you will just need to find their IDs which should be easy enough, you do e.g. a where clause with resource_id=1 and topic_id=2 and delete all those joining records matching the criteria. Is that what’s difficult?