×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Detect ON DELETE CASCADE in tables

Detect ON DELETE CASCADE in tables

Detect ON DELETE CASCADE in tables

(OP)
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.

RE: Detect ON DELETE CASCADE in tables

I think this should work for you:

CODE

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'; 

RE: Detect ON DELETE CASCADE in tables

(OP)
Thank you carp, works like a dream smile

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 * from dba_constraints, 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.

RE: Detect ON DELETE CASCADE in tables

CODE

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.

RE: Detect ON DELETE CASCADE in tables

(OP)
Thanks,
I like all_constraints a lot better since I have access to that data and don't have to bother my DBA smile


---- Andy

There is a great need for a sarcasm font.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close