Oracle identify if a delete will cascade

cascade, oracle

Solution

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.

Problem

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?

Original source