Oracle identify if a delete will cascade

Question

I am working on a legacy oracle database system (10g) and I do not have detailed schema information. I need to find out if deleting a particular record in a table will cause cascading deletes in other tables. I have checked for triggers. But, I am not sure about cascading due to referential constraints. Is there a simple way to identify this?

Answer 1

Assuming you know (or can determine) the foreign key constraint(s) involved, you can look at the DELETE_RULE column from DBA_CONSTRAINTS

SELECT constraint_name, delete_rule
  FROM dba_constraints
 WHERE r_constraint_name = <<name of the primary key constraint>>
   AND r_owner = <<owner of the primary key constraint>>
   AND delete_rule = 'CASCADE'

will show you all the foreign key constraints that refer to a particular primary key constraint and will cascade the deletes. If you care about constraints that will do a SET NULL when the parent row is deleted, you could look for rows where the delete_rule was SET NULL as well.

Note that if you do not have privileges on the DBA_CONSTRAINTS table, you can use ALL_CONSTRAINTS instead assuming that you're really only concerned with tables that you have SELECT privileges on.