0 
0 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
/
For those working on MySQL, I built a similar feature into a Perl script I recently wrote for MySQL. It’s published on MySQL Forge at http://forge.mysql.com/projects/view.php?id=157
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
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
Pete,
Thanks for the compliment and of course you can! I like that.
best regards,
Lucas
Lucas
A nice example; can I cite it my beginers giude to Analytics paper at UK OUG in November?