Deleting a list of entries that may violate foreign key constraint

Phoenix Forum Questions / Help
1

Good day, everyone!

So I’m having a little trouble trying to delete a list of entries from my database. Let’s say I have a Directories table and a Files table in a way that a directory may have many files, and a file belongs to a directory. So the files table will have a column directory_id to reference the directory.

In order to delete a directory, it has to be empty, which means no file should have its id as a foreign key. To achieve that, I added foreign_key_constraint function to Directory.changeset() function. My goal was to not raise an exception when attempting to delete a directory, and instead return a nice {:error, changeset} tuple. And it worked. If I have a directory, I can call Directory.changeset(directory) to get a changeset, and then try to Repo.delete it, which will have the desired result. If I just try to Repo.delete(directory) it raises because it did not have the constraint set up, I guess.

So far so good, but now I need to be able to delete multiple directories at once, given a list of directories ids.

I could very well just fetch all of them from the database and pass them to some Enum.each, where I would create the changeset and then delete it. This, of course, would create N+1 queries to delete N records, which is not exactly good.

I know Repo has the delete_all function, but calling it will raise the exception if one or more of the directories were not empty. Is there a way to make just one intelligent query that would delete all deletable but not raise an exception, and instead just tell me who the non deletable directories were?

A workaround could be to manually query for the deletable ones using a join, delete them with delete_all, and then fetch all non deletable ones and tell the caller those were the non deletable guys. This would be only two queries. I won’t have a Changeset with action: delete and the nice errors list, though.

Any ideias?

Thanks in advance! (:

2

Why would you define a foreign key constraint on the directory schema? A file belongs to a directory and should reference it, not the other way around.

3

I would say that is your best path, you will always need two queries minimum, one to delete, one to fetch, without breaking your first case

1 Like
4

I defined the foreign key constraint on the directory schema because when I try to delete a directory, an exception is raised from the database because of foreign key constraint. If I define it in the directory schema and then try do delete it, it returns {:error, changeset}.

5

Yeah, I guess you’re right. We’re gonna keep this implementation, then. Thanks!

6

All the *_constraint APIs of changesets convert errors in the DB to error messages on the changeset. Given the db only ever gives you a single error (the first encountered) you cannot really use those to aggregate errors for a list of changes.

1 Like
7

You can also do this from the has_one/many side of things using no_assoc_constraint

1 Like