Results 1 to 8 of 8
  1. #1
    Join Date
    May 2008
    Posts
    13

    Unanswered: on delete cascade

    hi,

    I have a parent and a child table relation.


    I want to delete a record from the parent table which has child records,
    but I dont know the child table name, to implement this.


    Is der sm way to delete parent record and child records at the same, without knowing the child table name??



    thanx in advance

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    > Is der sm
    Sorry I do not understand German well, so I do not know what you wanted to ask.

    Just reminded me, that after specifying foreign key constraint on the child column "on delete cascade" (the topic name), the child records will be deleted automatically when deleting the parent one.

  3. #3
    Join Date
    May 2008
    Posts
    13
    flyboy,

    d topic suggests a similar functionality i need,
    wat i actually require is deleting child records automatically when a parent record is deleted, even though on delete cascade is not
    mentioned

    the problem is i dont know the child table name, and i have to delete a parent record that has child records and on delete cascade option is absent
    Last edited by GRReddy; 05-09-08 at 08:42.

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    > i actually require is deleting child records automatically when a parent record is deleted, even though on delete cascade is not mentioned
    Is not it better to re-create the foreign key constraint with this option?

    Info about foreign key constraints is stored in DBA_CONSTRAINTS and DBA_CONS_COLUMNS system views, so you may get that info from them.

  5. #5
    Join Date
    May 2008
    Posts
    13
    though DBA_CONSTRAINTS can help find the child tables,
    ther are a large number of child tables for the same parent table, and as such finding which child record is preventing deletion of parent record is difficult

    so wantd to know if ther is any other way out

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    > and as such finding which child record is preventing deletion of parent record is difficult
    the raised exception contains info about preventing child table (the first one), so you may catch it and derive its name and column name from SQLERRM.

    > so wantd to know if ther is any other way out
    you shall query all child tables for existence of the parent record value. you may create a procedure for it - loop DBA_CONSTRAINTS/DBA_CONS_COLUMNS and query child tables dynamically (EXECUTE IMMEDIATE or DBMS_SQL).
    where would you suppose Oracle would store this duplicate info?

  7. #7
    Join Date
    May 2008
    Posts
    13
    flyboy

    thanx for the info

  8. #8
    Join Date
    May 2008
    Posts
    1
    You could use a database tool and reverse engineer your database to this tool and there you could see all the table relationships eiter parent or child, I develop such a tool it's called dbConstructor.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •