Foreign_key_constraint cannot handle when delete is nothing

Questions / Help
,
1

Hi, I have a problem which is not in adding or updating a record in database, just it returns in delete, hence it works in update and insert correctly.

My migration:

    create table(:blog_posts, primary_key: false) do
....

And my Blog author

  def change do
    create table(:blog_authors, primary_key: false) do
      add(:id, :uuid, primary_key: true)
      add(:post_id, references(:blog_posts, on_delete: :nothing, type: :uuid))
      add(:user_id, references(:users, on_delete: :nothing, type: :uuid))
    end
  end

now in blog_authors schema

  schema "blog_authors" do
    belongs_to :blog_posts, Post, foreign_key: :post_id, type: :binary_id
    belongs_to :users, User, foreign_key: :user_id, type: :binary_id
  end

And blog post schema

schema "blog_posts" do
  ...
  has_many :blog_authors, BlogAuthor, foreign_key: :post_id, on_delete: :nothing
end

Finally blog_authors changset

def changeset(struct, params \\ %{}) do
  struct
  |> cast(params, [:post_id, :user_id])
  |> foreign_key_constraint(:post_id, message: "msg error")
  |> foreign_key_constraint(:user_id, message: "msg error"))
  |> unique_constraint(:post_id, name: :index_blog_authors_on_post_id_and_user_id, message: "msg error")
end

But I still have this error:

** (Ecto.ConstraintError) constraint error when attempting to delete struct:

    * blog_authors_post_id_fkey (foreign_key_constraint)

After that, I even tested

 |> foreign_key_constraint(:post_id, name: :blog_authors_post_id_fkey, message: "msg error")

But it does not show my message and returns the error. How to fix this error?

Before sending this post I saw many topics in my research, and they suggest that change on_delete to all but I want to force user deletes the depended on record before delete main record

Now I have to use rescue because of this problem

Delete function:

  def delete_record(id) do
    Repo.get(Post, id)
    |> Repo.delete()
  end

Thanks

2

This is indeed what I would also do:

      add(:post_id, references(:blog_posts, on_delete: :delete_all, type: :uuid))

blog_authors is a join table whose job is to connect blog posts and their authors. If a blog post is deleted, it makes sense to also automatically delete all the entries in blog_posts that were connecting that post to its authors. There’s no reason to keep them around.

Can you explain why you’re not happy with this solution?

3

For example, you have a category and admin makes a mistake to delete a category, so with your way admin is going to delete all the posts of the category and I do not want this.

4

I wasn’t talking about post categories and you don’t mention categories in your example.

I didn’t say that on_delete: :delete_all is the right setting whenever you have a foreign key. I said that it’s the right setting for the example you posted.

5

You are right, I just wanted to give an example. But as I said please consider category instead of like, now how can I fix this problem when I need to force users to delete depended-table.

6

I’m sorry but I don’t quite understand what you mean by that. The original question you posted has IMO an easy answer (adding a on_delete: :delete_all), but apparently you’re after a different use case where this doesn’t work. Please provide an example of this use case, otherwise we can only speculate.

7

Hmm, sorry, I think you do not see a problem here because you want to change my on_delete structure, my question is clear, the admin client should delete all the authors of a post before deleting a post.
After that, can be able to delete a post.

I just need to fix this problem when it is on_delete: :nothing

Sorry about text I am not angry :)), maybe my text shows a wrong emotional because I am not native English language.

Thank you

8

Then perhaps your application code should do exactly that, explicitly delete the authors before deleting the post?

add the on_delete: :delete_all option to the join table as we discussed. That’s certainly not wrong: join table entries need to be deleted when one of their parents is gone.

Then add an authors association in blog_posts like this:

has_many :authors, through: [:blog_authors, :users]

this will allow you to directly access the authors of a post from the post.

Then your delete_posts function could look like this:

def delete_post(post_id) do 
   Repo.transaction(fn ->
    post =  Repo.get!(post_id) |> Repo.preload(:authors)
     
    for author <- post.authors, do: Repo.delete!(author)

    Repo.delete!(post)    
  end)
end

or something similar (using a Multi here would be better, but this is just to give the idea).

Problems with this approach?

1 Like
9

Thank you for all your efforts in advance.
I did this way in my project before, but I even need to show an error to admin user in the dashboard like:

"You may not delete this article because one or more authors may have already registered."

You mean with on_delete: :nothing we can not return foreignkey constraint message and the only way is rescue?

How can pass an error?

10 
def delete_post(Post{} = post) do 
   if Ecto.assoc(post, :authors) |> Repo.exists?() do
      {:error, :post_has_authors}
   else 
      Repo.delete(post)
   end
end

post = Repo.get(1)

case delete_post(post) do 
   {:ok, _} -> IO.puts("post deleted")
   {:error, :post_has_authors} -> IO.puts("please delete the authors first")
 
   error -> ... other error conditions
end

What about this?

Beware that you might have a race condition if somebody adds an author to the post while a user is trying to delete it - I don’t know if this an issue in your application. If it is, you should consider locking the post in the DB before deleting it.

1 Like
11

Hi @shahryarjb,

The foreign_key_constraint function is meant to be used from the insert/update side of things. If you try to insert/update something and the foreign key doesn’t exist, it records the error.

If you’d like to record the error from the delete side of things you need to use no_assoc_constraint.

5 Likes
12

I think you need no_assoc_constraint

5 Likes
13

wow, had no idea this existed! It will teach me to always look into the docs before trying to answer an Ecto question :smiley:

3 Likes