[DBCleaner] Foreign keys and already dropped tables

[DBCleaner] Foreign keys and already dropped tables

Forge Component
Published on 17 Mar by Acácio Porta Nova
21 votes
Published on 17 Mar by Acácio Porta Nova
HI Ricardo, 

We've tested your component. Apparently the generated script it cannot be run at once because foreign keys are not taken into consideration when generating the script.
We have dependencies between our obsolete tables, so the order by which we drop them is relevant.

I've inspected your code, and I believe we could benefit from joining the generated entities to drop, with system tables to prevent generating drop statements for entities that no longer exist, and to order by number of foreign key references, minimizing probability of failed statements.

Kind Regards,
?João Grazina

I'll probably add a check to the generated script to avoid dropping non-existing tables.

Regarding the foreign keys, it's a more complex issue which I'll have to think about how best to approach.

Best regards,
Ricardo Silva
I tried this yesterday, and I had the same issues.

I bet that a simple way to handle this in many cases (perhaps not "most" though), is before dropping the tables, drop all of the foreign keys they have. Do all FKs first, and then try to drop the tables. If we are talking about getting rid of the tables for a deleted eSpace, that will help a lot.