How to show error when category has some post with foreign_key_constraint

Hello, when I want to delete a category I have just deleted the category always with on_delete: :delete_all and because I used that all of my post were deleted, but this time I need to show an error massage to my client when they need to delete some category they should delete all post of the category first and next can delete category.

now I change my reference in migration


add :category_id, references(:blog_categories, on_delete: :delete_all, type: :uuid)


add :category_id, references(:blog_categories, on_delete: :nothing, type: :uuid)

now I add it to my Category changeset

|> foreign_key_constraint(:category_id, message: "this category has some post and you can't delete it")

but it doesn’t work and shows me an error:

** (exit) an exception was raised:
    ** (Ecto.ConstraintError) constraint error when attempting to delete struct:

    * blog_posts_category_id_fkey (foreign_key_constraint)

If you would like to stop this constraint violation from raising an
exception and instead add it as an error to your changeset, please
call `foreign_key_constraint/3` on your changeset with the constraint
`:name` as an option.

I just want to show a custom error I need how can do it ?


Can you please tell us how you try to delete the record?

Also it seems, as if you also need to provide name: :blog_posts_category_id_fkey as an option to the constraint (or does the name need to be a string?).

1 Like

thank you, sure

my controller:

  def delete_category(conn, %{"id" => id}) do
    case BlogQuery.delete_category(id) do
      {:ok, :delete_category} ->
        |> put_flash(:info, "category ok")
        |> redirect(to: "/categories")
			_ ->
        |> put_flash(:error, "category NoOk")
        |> redirect(to: "/categories")

my query:

  def get_category_by_id(id) do
    Repo.get(BlogCategorySchema, id)

  def delete_category(id) do
    with {:ok, category_id} <- Ecto.UUID.cast(id),
          %Omran.Blog.BlogCategorySchema{} = category <- get_category_by_id(category_id) do
      {:ok, :delete_category}
      {:error, msg} ->
        {:error, :delete_category, msg}
      _ ->
        {:error, :delete_category, "Category dosent exist"}

my html:

<%= link "delete", to: Routes.admin_path(@conn, :delete_category,, method: :delete, data: [confirm: "are u sure ?"] %>

I add this but it doesn’t work

defmodule Omran.Blog.BlogCategorySchema do
  use Ecto.Schema

  import Ecto.Changeset
  @primary_key {:id, :binary_id, autogenerate: true}
  @foreign_key_type :binary_id

  schema "blog_categories" do

    field :title, :string, size: 150, null: false
    field :description, :string,  null: false
    field :seo_alias_link, :string, null: false
    field :seo_words, :string, size: 150, null: false
    field :seo_description, :string, size: 150, null: false
    field :status, :boolean, null: false

    has_many :blog_posts, Omran.Blog.BlogPostSchema,  foreign_key: :category_id, on_delete: :nothing

  @all_fields ~w(title description seo_alias_link seo_words seo_description status)a
  def changeset(struct, params \\ %{}) do
    |> cast(params, @all_fields)
    |> validate_required(@all_fields)
    |> foreign_key_constraint(:category_id, name: :blog_posts_category_id_fkey, message: "لطفا اول مطالب این مجموعه را حذف کنید بعد خود مجموعه را")
    |> unique_constraint(:seo_alias_link, name: :unique_index_on_blog_categories_alias_link, message: "alias link already exists.")


No changeset involved. So nothing you add, remove or change in your changeset would change anything. You need to pass category through the changeset that has the constraint as far as I understand the changeset stuff.

1 Like

unfortunately I have no idea to fix this, would you mind giving an example with code please?

if the category doesn’t have any post it can be deleted when I have this error it has some post

As far as I understand it:

|> Category.delete_changeset_or_whereever_you_have_that_constrained_defined
|> Repo.delete

PS: As you do not need to check all the other validations and constraints, it might even be enough to

|> Ecto.Changeset.foreign_key_constraint(:category_id, message: "this category has some post and you can't delete it")
|> Repo.delete

You might need to play a bit with the options anyway, as I am not sure how that particular constraint works

1 Like

Okey I think add more checker on my with Condition

with {:ok, category_id} <- Ecto.UUID.cast(id),
          %Omran.Blog.BlogCategorySchema{} = category <- get_category_by_id(category_id) do

I thought I can add that foreign_key_constraint and fix this.

Thank you

I have no clue what you mean by this, but on a first glance it reads like you want to check whether or not posts exist before deleting. This is subject to a race. You should strictly avoid this.

Use the constraint.

PS: As far as I know, calling into Repo directly from the controller is considered bad practice, one should only use the appropriate context modules.

1 Like