How to handle constraint error when deleting record from adjacency list?

Can You show your categories migrations?

There should be something like this…

      add :parent_id, references(:categories, on_delete: :delete_all)

def change do
    create table(:categories) do
      add(:name, :string)
      add(:parent_id, references(:categories), null: true)
      timestamps()
    end

    create(index(:categories, [:parent_id]))

    create(
      index(:categories, [:name, :parent_id],
        unique: true,
        name: :unique_categories_name_parent_id
      )
    )

    create(
      index(:categories, [:name],
        unique: true,
        where: "parent_id IS NULL",
        name: :unique_categories_name_parent_id_null
      )
    )
  end

Have you thought about soft delete? I feel this would solve your problem and give you a bonus to restore deleted categories.

I want to inform the user that the category has sub-categories and should not be deleted.

Maybe no_assoc_constaint

A Changeset is just a validation mechanism and there’s nothing inherent in them that prevents their use in deletion. I do agree that most people focus on insert and update when discussing Changesets, but validation is validation and there can be reasons to validate delete operations including your scenario. You’ll see that Ecto.Repo.delete/2 can take a Changeset: Ecto.Repo — Ecto v3.14.0.

Personally, I like the idea of Changesets and handling validation prior to attempting operations on the database, even for deletes… though I’d likely have delete-specific Changeset checks. It might be overly defensive on this, because I also absolutely believe that it’s not a substitute for correct constraints in the database, too, but by validating in the application I feel like I’m more in control. I also like raised exceptions to be logged and not depending on them for my logic means that I can be very consistent about that without thinking too much about it.

As far as I know, this is the idiomatic solution. Another option is to manually catch the exception within a try/rescue block, but this method is rather ugly so I’d recommend to use it as a last resort.

no_assoc_constraint will indeed give you nice Changeset error that you can directly work with in forms.

But if for one reason or another that’s not an option for you then you could always take the direct route:

category_id = 123 # desired parent id goes here
query = from(c in Category, where: c.parent_id == ^category_id)
children_count = Repo.aggregate(query, :count, :id)
if children_count > 0 do
  # construct and/or handle an error here and act on it
else
  # all good, you can delete the category
end

But isn’t this in effect just Ecto.Changeset.foreign_key_constraint/3 except performing the foreign key check fully manually in advance?

My understanding is that database constraint validations (unique, foreign key, etc.) in Changesets aren’t checked in advance and rely on database error reporting to return a Changeset error post-execution attempt. Using a Changeset here with a foreign_key_constraint/3 check allows the database constraint to abort the database transaction on query with the appropriate error where Ecto picks it up and report it as a Changeset error. So while what your saying will work, I’m not sure I see the advantage to handling that way rather than submitting a Changeset… two queries vs. one.

So, using a changeset and no_assoc_constraint doesn’t work for me.

 def delete_category_by_id(id) do
    Repo.one!(from(c in Category, where: c.id == ^id))
    |> Ecto.Changeset.change()
    |> Ecto.Changeset.no_assoc_constraint(:categories)
    |> Repo.delete()
  end

I get the following error:

cannot add constraint to changeset because association categories does not exist. Did you mean answers?