Putting Analytical Functions to good use – find tables with multiple foreign keys to the same referenced table

Analytical Functions are a joy in my life as an Oracle developer. I have been demonstrating them, presenting on them, teaching on them and using them. Today I had a good opportunity for the latter. Working with Oracle Designer and the BC4J Designer Migration tool (incorporated in the JHeadstart Designer Generator) I ran into a known bug – JDeveloper bug 4284270 – that basically results in problems with Table Definitions having multiple foreign keys to the same referenced table.

In order to find out the magnitude of the problem the migration would produce for me, I wanted to find out about which tables – and which foreign keys – I had to deal with. Using the Analytical LAG and LEAD function provided the perfect solution for this particular problem.

In the database, the following query against the Data Dictionary will bring up all tables with more than one Foreign Key to the same Referenced Table – it will list every Foreign Key involved in a reference to a table that is referenced by another foreign key from the same table as well:

select table_name
,      foreign_key
,      referenced_table
from ( select fk.table_name
       ,      fk.constraint_name foreign_key
       ,      pk.table_name referenced_table
       ,      lead(pk.table_name) over (partition by fk.table_name order by pk.table_name) next_reffed_table
       ,      lag(pk.table_name) over (partition by fk.table_name order by pk.table_name) previous_reffed_table
       from   user_constraints fk
       ,      user_constraints pk
       where  fk.r_constraint_name = pk.constraint_name
       and    fk.constraint_type   = 'R'
     )
where  next_reffed_table = referenced_table
or     previous_reffed_table = referenced_table
order
by     table_name
,      referenced_table
/
 

The query returns all Foreign Keys with the Detail (the referencing) Table and the Master (or referenced) Table. For each record it also reads the referenced table for the next (LEAD) and previous (LAG) foreign key owned by the same referencing table (partition by fk.table_name). We then filter to only retain the records where either the next referenced table or the previous referenced table is the same as the currently referenced table. Whenever two or more records for foreign keys from the same detail table reference the same master table, we will have records returned by this query.

Multiple Foreign Keys in Oracle Designer 

Since I had to investigate against the Designer Repository – which also has information about Foreign Keys defined against Views – I needed to use a query executed against the Oracle Designer API, very much along the same lines:

begin
  jr_context.set_workarea('YOUR_WORKAREA');
end;
/

select table_name
,      foreign_table_name
,      fk_name
from (
       select tab.name table_name
       ,      fco.name fk_name
       ,      tab_foreign.name foreign_table_name
       ,      lead(tab_foreign.name) over (partition by tab.name order by tab_foreign.name) reffed_tbl_next_fco_in_tbl
       ,      lag(tab_foreign.name) over (partition by tab.name order by tab_foreign.name) reffed_tbl_previous_fco_in_tbl
       from   ci_relation_definitions tab
       ,      ci_relation_definitions tab_foreign
       ,      ci_foreign_key_constraints fco
       ,      ci_application_systems fol
       ,      ci_folder_members fm
       where  fol.name = 'THE_FOLDER_OR_APPLICATION_SYSTEM'
       and    fm.folder_reference = fol.id
       and    fm.member_object = tab.id
       and    fco.table_reference = tab.id
       and    fco.foreign_table_reference = tab_foreign.id
     )
where  foreign_table_name in ( reffed_tbl_next_fco_in_tbl, reffed_tbl_previous_fco_in_tbl)
order
by     table_name
/