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 /
- SQL Question: Find overlap in periods – using Analytical Functions (LAG and LEAD)
- The minimum number of statements required for Inserting Records from Two Source Tables into Four Target Tables – Introducing Multi Table Insert
- Analytical SQL Functions – theory and examples – Part 1 on the Partition Clause
- The Next Value – How much higher is my salary than the next in (Dense) Rank – Oracle Analytical Functions
- Parallelizing Table Functions (instead of paralysing)