> 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.
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
> 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?
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.