File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes Oracle/OAS and the fly likes tree traversal through SQL Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of Java Interview Guide this week in the Jobs Discussion forum!
JavaRanch » Java Forums » Products » Oracle/OAS
Bookmark "tree traversal through SQL" Watch "tree traversal through SQL" New topic

tree traversal through SQL

deepak carter
Ranch Hand

Joined: Feb 19, 2011
Posts: 165

I am working on one requirement in which i have to delete the parent table but cannot since it depends on other table and hierarchy continues.Basically its like a tree structure

So my doubt is can we implement a tree traversal algorithm in sql or pl/sql .If yes then how or any other approach.

Thanks in advance
chris webster

Joined: Mar 01, 2009
Posts: 2296

I don't really understand what you're trying to do here.

If you are really going to "delete" a table - i.e. drop the table completely - then you need to decide what to do with any data in the table (remove it or move it somewhere else) and also what to do with any related data e.g. data in tables that have foreign keys to your table (do you want to cascade the deletion to these child tables?).

If you really mean that you just want to delete individual records from your parent table, then you still need to decide what to do about any child records with foreign keys i.e. you need to look at the foreign keys that depend on the main table and figure out what to do with the related data if you delete the parent records.

Either way, you need to think about how you will maintain your data integrity after you delete the records or drop the table.

Finally, if you're looking at a general idea of running hierarchical queries, remember that Oracle allows you to execute "tree" queries using CONNECT BY syntax.

No more Blub for me, thank you, Vicar.
I agree. Here's the link:
subject: tree traversal through SQL
jQuery in Action, 3rd edition