Detect ON DELETE CASCADE in tables 1

[Andrzejek]

I have a table with PK field, and several other tables that have relation to this field as a Foreign Key field. The FK should be set up with ON DELETE CASCADE

Is there an easy way to detect if all those Foreign Keys fields are set up with ON DELETE CASCADE ?



---- Andy

There is a great need for a sarcasm font.

 

[carp]

I think this should work for you:

SELECT c1.table_name, c1.delete_rule
  FROM dba_constraints c1
       INNER JOIN dba_constraints c2
          ON c1.r_constraint_name = c2.constraint_name
 WHERE c1.constraint_type = 'R' 
   AND c2.table_name = 'PARENT_TABLE_NAME';

 

[Andrzejek]

Thank you carp, works like a dream

Is there a way to add a field to this request to display a constraint's name?
I would figure it out myself by just asking for [tt]* from dba_constraints[/tt], but since this is a DBA object - I don't have access to it - I had to ask my DBA to run it


---- Andy

There is a great need for a sarcasm font.

 

[carp]

SELECT c1.table_name, c1.constraint_name, c1.delete_rule
  FROM dba_constraints c1
       INNER JOIN dba_constraints c2
          ON c1.r_constraint_name = c2.constraint_name
 WHERE c1.constraint_type = 'R' 
   AND c2.table_name = 'PARENT_TABLE_NAME';

If you want to run this yourself, try replacing all references to "dba_constraints" with "all_constraints". Assuming all of the tables involved are visible to you, this should also show you constraints.

 

[Andrzejek]

Thanks,
I like [tt]all_constraints[/tt] a lot better since I have access to that data and don't have to bother my DBA


---- Andy

There is a great need for a sarcasm font.