Ok, this time a quick not so exciting post. Actually, I find my self recreating a query again, that I created many times in my carreer. So, why not post it?

Last year, I published my Darwin Object Type Accelerator (Dotacc). It allows you to generate objects from a datamodel. What it also does is create collection types for tables that refer to the tabel you want to generate an object for. For some you want that, but for others you don't. Simply because you don't need them to be queried along. Therefor, I added functionality to disable those.

But then comes the question: which are the tables with their foreignkey constraints that refer to this particular table?

The answer is in the ALL_CONSTRAINTS view (with the variants of DBA_% and USER_%).
There are several types of constraints:

• C: Check constraints
• R: Referential -> the particular foreign keys
• P: Primary Key
• U: Unique Key

I'm interested in the Foreign keys, thus those where constraint_type='R'. But those refer not to a table but to another constraint. So, I need to get the primary key, constraint_type='P', of the table that I want to query and join those together.

That get's me:

select fk.* 
from all_constraints  fk
join all_constraints rpk on rpk.constraint_name = fk.r_constraint_name 
where fk.constraint_type='R'
and rpk.constraint_type in ('P', 'U')
and rpk.table_name = 'DWN_MY_TABLE';