Re: FK-constraints without corresponding index, how to find with SQL ?

In article <4a1lqh$orj_at_axl02it.ntc.nokia.com>

           lassi.salo_at_ntc.nokia.com "Lassi Salo" writes:

: Is there a way find out which FK-constraints don't have a : corresponding index (same columns in the same order)?

I suppose that you do not want to write a script that tries to insert valid rows into each table in turn and checks to see which inserts result in a parent table lock.

If you are trying to find out which parent tables could suffer this problem without actually doing the above, your problem may be nastier than you think.

An index which 'satisfies' the foreign key constraint need not have the same columns in the same order. In fact it could have more columns than the constraint anyway just so long as the N constraint columns appear somewhere in the first N columns of the index.

Finally, as you probably guessed, the index need not be owned by the owner of the table, and need not have the name of the constraint.

Unless you go procedural you may need something like (and I'm not going to worry about syntax, etc):

    select cons1.constraint_name, cons1.table_name, cons1.owner     from dba_constraints cons1
    where

        cons1.contraint_type = 'R'
    and not exists

        (select ind1.index_name from dba_ind_columns ind1
         where ind1.table_owner = cons1.owner
         and ind1.table_name = cons1.table_name 

    Start looking for column names that match the column     names listed in the constraint.
*/

         and ind1.column_name in (
             select column_name from dba_cons_columns cc1
             where cc1.owner = cons1.owner
             and cc1.table_name = cons1.table_name
             and cc1.constraint_name = cons1.constraint_name
        )

    Only check the first N columns of the index, where N is     the total number of columns defined in the constraint
*/

        and ind1.column_position <= (
            select count(*) from dba_cons_columns cc2
             where cc2.owner = cons1.owner
             and cc2.table_name = cons1.table_name
             and cc2.constraint_name = cons1.constraint_name
       )
       group by ind1.index_name

    number of columns found must match number of columns in constraint
*/

       having count(*) = (
            select count(*) from dba_cons_columns cc3
             where cc3.owner = cons1.owner
             and cc3.table_name = cons1.table_name
             and cc3.constraint_name = cons1.constraint_name
      )
      ;

Hope this helps:---

Any comments on the opening opinions welcomed.

-- 
Jonathan Lewis