Under review

DROP DDL statements are not hierarchical

Alan Nitikman 1 year ago • updated 1 year ago 3

How come your generated 'DROP' DDL statements are not in the right sequence?  Constraints must be dropped first, then the tables. Child Table first, then Parent table. It should be intelligent enough to build the DDL correctly, based on hierarchy.


Can you send invalid drop script to contact@vertabelo.com?

Our order in the DROP DDL is:
1. Drop views.
2. Drop foreign keys.
3. Drop tables.
4. Drop sequences.

I think above solution should be correct.

After removing all constraints between tables, an order of 'DROP TABLE' statements does not matter.

Best regards,

Adam Mościcki

What you say is true, but it's the constraints that are not sequenced correctly. The child constraints must be dropped before the parent constraints, or it will fail.  We will update with an example, but that's our issue. As created by the tool, the DROP sql will fail.  

Also, why do you NOT have IF EXISTS statements for the DROPs?  That is an easy addition, then you could include the DROPs up front in the DDL and we wouldn't have to mess with failed DROPs and multiple SQL files.  All the major data modeling tools I've used have allowed us to generate a DROP/CREATE sql with an uncheck option if we're doing it for the first time in that environment. But the IF EXISTS would allow us not to worry about the DROPs unless they're needed.

First things, first, please consider a reasonably complex model and how you generate your constraint statements, in what order? We will get you an example as soon as possible, but the constraint sequence is the problem.