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

5

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
/
 
Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

5 Comments

  1. Roland,

    If all I needed is the name of the table with multiple foreign keys then of course you are right. However, I also want to know the names of the foreign keys. I believe that necessitates the use of the LAG and LEAD functions.

    You are right that filtering on constraint_type is not really necessary.

    Thanks for your comment.

    best regards,

    Lucas

  2. Hi Lucas,

    nice usage of LAG and LEAD. I think these analytical functions are very powerful and quite useful from time to time.

    But couldn’t the original problem, “Table Definitions having multiple foreign keys to the same referenced table”, be solved using a simpler (and probably faster) method?

    select fk.table_name
    from user_constraints fk
    , user_constraints pk
    where fk.r_constraint_name = pk.constraint_name
    and fk.constraint_type = ‘R’
    group by fk.table_name
    , pk.table_name
    having count(*) > 1

    (The fk.constraint_type = ‘R’ condition is not really necessary, because r_constraint_name will be NULL for any but foreign key constraints, but it does tend to perform better when it is included)

    Roland Bouman