Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

tree traversal through SQL

 
deepak carter
Ranch Hand
Posts: 165
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi ALL,

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
Bartender
Posts: 2407
32
Linux Oracle Postgres Database Python Scala
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic