Troubleshoot errors of type: violates foreign key constraint

rfernandez

Hi,
Sometimes when trying to delete objects in Forms or even Forms themselves, or Functions I get errors like this:

ERROR: update or delete on table "form" violates foreign key constraint "open_form_form_id_open_fkey" on table "open_form" (SQLSTATE 23503)

Logic indicates that this is because the entitity is in use somewhere else. However this happened to me when trying to delete an empty container (not very logic).
I can leave the element there and not use it anymore, however I like to keep the house clean, and delete unused objects, so ...

How do you suggest I troubleshoot this?
I can go directly to the table referenced in the error and with the object ID try to see what is the problematic relationship and do something about it. Sounds dangerous.

In the cases that makes sense I usually find the offending counterpart in the UI and solve it there, but other times there is no such part (for example when deleting an empty container)
In this case seems like some information in the table got "wrong".
On the other hand as the application grows this gets worse, in that it is harder to find anything at that detail level.

Your help will be much appreciated!!

Thank you!!
Warm regards!

gabriel

Sorry that you are having issues.

The table open_form does not have any references to fields, such as container fields. The foreign keys are strictly enforced so a "confusion" or "corruption" of references is highly unlikely.

When the table open_form complains about a foreign key to a form being violated, a form is about to be deleted that is still referenced. I know that does not match your experience; working for years with r3 and troubleshooting these kinds of issues in the past, it was always a misunderstanding in the end - the DB error has not lied to me yet.

Of course we want to make it easier, to identify references for more straightforward cleanup. We have done so with attributes but are still some ways of to implementing this for all entities, such as forms & fields.

But to your issue: If you could export the app and send it to me, I could have a look. Would that be possible?

rfernandez

No worries, please!!
The application complaining is in a learn/test instance, and I'm usually prone to push things to the edge :-), so it is probably my fault.
I just wanted to know if there was some methodology to troubleshoot this kind of situation, like looking at certain logs, or so.
I don't want you to spend your time on this, as I'm aware that you are very busy. So, if you have any tips regarding where and what to look for, they will be welcome, otherwise please don't worry.

On the bright side, I wanted to tell you that I'm doing nice progress on my understanding of the platform, as you will probably see in other questions I have recently posted. ;-)
Still a long journey ahead, but I'm starting to feel comfortable with the platform, and some small apps I've built have been very well received by my workmates.

Thank you very much!!
Best regards
Ricardo

gabriel

rfernandez I just wanted to know if there was some methodology to troubleshoot this kind of situation, like looking at certain logs, or so.

Besides attributes, which will list all references when you attempt to delete them, many other entities (fields, collections, etc.) do not offer this.

It´s on our list to work on the Builder, add more messages, show references, clean things up, etc. Together with some other house-keeping and some long-term features, it keeps being postponed as we are a small team and features being paid by customers need to take priority. Good news is that the customer base keeps growing, giving us more resources and options the longer we operate.