How do I find which tables have foreign keys on my table?

Question

Is there a query I can do to find which tables have foreign keys on a given table? Our DBA does not believe in (or understand?) "ON DELETE CASCADE", so I when I delete something from a table, I want to make sure I delete all the dependent stuff first.

(Note, I don't need to find the tables programmatically, I can do that in SQL*Plus.)

Sounds more "does not believe". ON DELETE CASCADE does the same as looking into constraints and delete reference records. — Christian13467
– Christian13467, Commented Sep 16, 2010 at 14:28
However, ON DELETE CASCADE is notoriously slow; if it's a large job, it's usually better to delete all of the children first. — Adam Musch
– Adam Musch, Commented Sep 16, 2010 at 15:59

RC. · Accepted Answer · 2010-09-16 15:16:21Z

2

SELECT dc.constraint_name, dc.constraint_type, dc.owner, dc.table_name FROM dba_cons_columns dcc JOIN dba_constraints dc ON (dcc.constraint_name = dc.r_constraint_name and dc.owner = dcc.owner) WHERE dcc.owner = 'OWNER_NAME' and dcc.table_name = 'TABLE_NAME';

edited Sep 16, 2010 at 15:16

answered Sep 16, 2010 at 14:44

RC.

28.4k10 gold badges80 silver badges94 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Paul Tomblin Over a year ago

I had to change the second line to

FROM dba_cons_columns dcc JOIN dba_constraints dc ON (dcc.constraint_name = dc.r_constraint_name and dc.owner = dcc.owner)

because multiple owners have the same constraint names.

Kirill Leontev · Accepted Answer · 2010-09-16 14:28:04Z

1

Check all_constraints and all_cons_columns dictionaries.

answered Sep 16, 2010 at 14:28

Kirill Leontev

11k8 gold badges46 silver badges50 bronze badges

2 Comments

Paul Tomblin Over a year ago

I don't see a column in those dictionaries that tell you which table the foreign key is for, so if I select * from all_constraints where table_name = 'CLIENT' and it shows me all the indexes and constraints on CLIENT, but I can't find a query that shows me which tables have a foreign key on CLIENT.

Paul Tomblin Over a year ago

Oh, but if I select * from all_constraints where r_constraint_name = 'PK__CLIENT' it does what I want. Mostly.

Collectives™ on Stack Overflow

How do I find which tables have foreign keys on my table?

2 Answers 2

1 Comment

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Related