1

My requirement is to delete data from couple of table selected dynamically based on search condition.

So my cursor should fetch tables in their parent-child hierarchy so that it will not give exception 'CHILD RECORD FOUND' while deleting records.

lets take exable

Table A is child of Table B

Table B is child of Table C

Table D is child of table G

So it should delete in this sequence.

A then
B or D then
D or G

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
Anand
  • 483
  • 1
  • 4
  • 7
  • 1
    Which DBMS are you using? Postgres? Oracle? –  Oct 06 '13 at 19:20
  • Are you working with a specific group of tables whose dependencies you know, or are you trying to write a more general utility that can handle any table? – Ann L. Oct 06 '13 at 20:14
  • It's generic script that should handle all the tables in schema – Anand Oct 07 '13 at 10:47

1 Answers1

0

If you use cascading foreign keys, you don't have to worry about table order. Just delete from the top of the hierarchy (table G in in your example) and then all dependent rows in the descendent tables will be deleted atomically.

Read more about Cascading Referential Integrity Constraints.

Re your comment:

Also it should be generic enough to handle delete even if delete cascade is not there and we do not have knowledge of dependencies.

See these questions for tips on discovering constraints in Oracle:

Community
  • 1
  • 1
Bill Karwin
  • 538,548
  • 86
  • 673
  • 828
  • The tag `sql` only refers to the *query language*, it does not refer to the DBMS product "SQL Server". For that the tag `sql-server` is intended. –  Oct 06 '13 at 19:20
  • @a_horse_with_no_name, I know, but many users of Microsoft SQL Server have the habit of tagging their questions simply `sql` because they think that's the name of the product they use. They also think "word" refers to their text editor. – Bill Karwin Oct 06 '13 at 19:21
  • I'm sorry for the confusion about DBMS. It's oracle DBMS. Also it should be generic enough to handle delete even if delete cascade is not there and we do not have knowledge of dependencies. – Anand Oct 07 '13 at 10:46
  • I went through suggested links but if tables have circular dependencies then this solution does not work. Any solution that can avoid circular dependencies if any.. – Anand Oct 11 '13 at 17:27
  • Like any other recursive search through cyclic data structures, you just have to keep track of the items you've seen before and exclude these from the search. Sorry I don't have a reference for you on that one. – Bill Karwin Oct 11 '13 at 17:32