DROP selected items and generate DROP constraints

Hi!

At least a few months ago I was able to select all the tables and sequences in an Oracle schema from the Database view, choose Drop and it would generate a SQL statements that would include dropping foreign key constraints as well. Otherwise the drop statements would fail due to the constraints.

I remember Datagrip would create these required drop constraint statements only when I selected the tables and sequences in a specific order. If I did it in another way/order there would only be the drop table and drop sequence statements without the drop constraint statements.

Meanwhile my brain has forgotten the order and Datagrip has upgraded and I cannot figure out how to tell Datagrip to do its magic and add the drop constraints for me.

I also noticed the resulting SQL now also include a forward slash on a separate line after each drop statement as well like in a script instead of a list of statements with semicolons. I remember this being different previously but I might very well be wrong about this.

What's the correct way to tell Datagrip to drop selected items and also generate any other necessary drop statements too. I know this feature existed a few minor versions / months ago.

DataGrip 2017.3.4
Build #DB-173.4301.31, built on January 17, 2018
Licensed to XXXXXXXXXXXXXX
Subscription is active until November 29, 2018
JRE: 1.8.0_152-release-1024-b11 amd64
JVM: OpenJDK 64-Bit Server VM by JetBrains s.r.o
Linux 4.13.0-32-generic

7 comments
Comment actions Permalink

Same here with intellij. Before an update at the end of last year or around, I was able to delete all the table like that. Intellij would have generate all the drop constraint and the drop table in the right order.

0
Comment actions Permalink

We care about the order, but we do not generate additional DROPs for constraints. Can you please check in 2017.3.5? The order should be ok.

0
Comment actions Permalink

Hi!

Thanks for the reply.

 

Updated to 2017.3.5 just now.

The selection order of tables and the generated DROP TABLE statements in the confirmation window do not seem to correlate. Looks random to me, sorry.

However I don't really care about the order.

 

I know for certain DataGrip did generate DROP CONSTRAINTS previously. If this wasn't done by DataGrip explicitly then by a library it used/uses. This was confirmed by Loius Andriet above, apparently I'm not making this up.

 

If this is relevant at all then the driver configured is Oracle [latest]:

.DataGrip2017.3/config/jdbc-drivers/Oracle/12.1.0.2/ojdbc6-12.1.0.2.jar
.DataGrip2017.3/config/jdbc-drivers/Oracle/12.1.0.2/oracle-driver-license.txt
.DataGrip2017.3/config/jdbc-drivers/Oracle/12.1.0.2/xdb6-12.1.0.2.jar
.DataGrip2017.3/config/jdbc-drivers/Oracle/12.1.0.2/xmlparserv2.jar

Connecting to Oracle 12.1 EE server. Why ojdbc6 though?

0
Comment actions Permalink

Are you talking about constraints in OTHER tables referencing the needed one? Did you see the actual code which is going to be executed? Can you remember which version was it?  

0
Comment actions Permalink

> Can you remember which version was it?  

Sorry, I can't give you the exact version number. As best as I can remember I last used this feature in December. It was certainly there in the last EAP build and probably a release or maybe two later as well.

 

> Did you see the actual code which is going to be executed?

Yes.

My use case was I selected ALL tables and sequences in an Oracle schema and if I did it in a certain/correct order the drop command would generate a DROP statement for all selected tables, sequences and also add some drop constraints statements as well in the interspersed in the correct locations.

I could always check in the confirmation dialog window that I selected the tables and sequences in the "correct" order before actually executing them. This was easy to see.

 

> Are you talking about constraints in OTHER tables referencing the needed one?

Yes. Extra SQL code was generated that used the constraint's name. The number of lines on SQL in the confirmation dialog window increased.

 

The only caveat is that maybe this was not done using a separate "DROP CONSTRAINT <constraint_of_some_other_table>" before the affected DROP table but somehow else. Though I wouldn't know how then. I clearly remember the constraint names in the generated SQL code when the selection was done in the "correct" order.

0
Comment actions Permalink

Ok, I got you. Before, if you SELECT all tables, we dropped constraints because we didn't know about connections. No we do, and we should make the proper order. Why does not right order solve your problem?

(If the order is wrong, that's the bug we need to fix)

 

 

0
Comment actions Permalink

> Ok, I got you. Before, if you SELECT all tables, we dropped constraints because we didn't know about connections. No we do, and we should make the proper order. Why does not right order solve your problem?

Ok, got it. I had the problem with the 2017.3.4 version. I haven't tested actually deleting  with the .5 version as I wasn't aware this was solved by order instead of drop constraints. After all I was looking for the drop constraint statements as before, not the order of the statements.

Thanks for clearing this up.

So assume this is fixed in 2017.3.5 and I'll report if there's still an issue. Otherwise case closed, thanks.

0
Comment actions Permalink

We've discovered the problem. It has been already fixed for 2018.1 (because we rework all the code generation), and we've just fixed it in 2017.3. Expect what you need whether in EAP or in bug-fix update. Thank you very much! 

0

Please sign in to leave a comment.