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.
Migration:
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
end
create unique_index(:join_resources_topics, [:resource_id, :topic_id], name: resource_topic_index)
end
Migration Questions:
- :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.
- :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?
Schema:
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
end
@doc false
def changeset(join_resource_topic, attrs) do
join_resource_topic
|> cast(attrs, [:resource_id, :topic_id])
|> validate_required([:resource_id, :topic_id])
end
end
Issue:
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
join_resource_topic
|> 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
end
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?
