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

From: Thomas J Kyte <tkyte_at_us.oracle.com>
Date: 1995/12/05
Message-ID: <4a1tik$m7j_at_inet-nntp-gw-1.us.oracle.com>#1/1

Lassi Salo <lassi.salo_at_ntc.nokia.com> wrote:

Here is a sql*plus script that will do it on a user by user basis:

• unidx.sql --------------- create or replace view tmp_indexes as select table_name, index_name, max(decode(column_position, 1, column_name,NULL)) || max(decode(column_position, 2,', '||column_name,NULL)) || max(decode(column_position, 3,', '||column_name,NULL)) || max(decode(column_position, 4,', '||column_name,NULL)) || max(decode(column_position, 5,', '||column_name,NULL)) || max(decode(column_position, 6,', '||column_name,NULL)) || max(decode(column_position, 7,', '||column_name,NULL)) || max(decode(column_position, 8,', '||column_name,NULL)) || max(decode(column_position, 9,', '||column_name,NULL)) || max(decode(column_position,10,', '||column_name,NULL)) || max(decode(column_position,11,', '||column_name,NULL)) || max(decode(column_position,12,', '||column_name,NULL)) || max(decode(column_position,13,', '||column_name,NULL)) || max(decode(column_position,14,', '||column_name,NULL)) || max(decode(column_position,15,', '||column_name,NULL)) || max(decode(column_position,16,', '||column_name,NULL)) columns from user_ind_columns group by table_name, index_name /

create or replace view tmp_constraints
as
select a.table_name, a.constraint_name,

	   max(decode(position, 1,     column_name,NULL)) || 
	   max(decode(position, 2,', '||column_name,NULL)) || 
	   max(decode(position, 3,', '||column_name,NULL)) || 
	   max(decode(position, 4,', '||column_name,NULL)) || 
	   max(decode(position, 5,', '||column_name,NULL)) || 
	   max(decode(position, 6,', '||column_name,NULL)) || 
	   max(decode(position, 7,', '||column_name,NULL)) || 
	   max(decode(position, 8,', '||column_name,NULL)) || 
	   max(decode(position, 9,', '||column_name,NULL)) || 
	   max(decode(position,10,', '||column_name,NULL)) || 
	   max(decode(position,11,', '||column_name,NULL)) || 
	   max(decode(position,12,', '||column_name,NULL)) || 
	   max(decode(position,13,', '||column_name,NULL)) || 
	   max(decode(position,14,', '||column_name,NULL)) || 
	   max(decode(position,15,', '||column_name,NULL)) || 
	   max(decode(position,16,', '||column_name,NULL)) columns

from user_cons_columns a, user_constraints b where a.constraint_name = b.constraint_name and b.constraint_type = 'R'
group by a.table_name, a.constraint_name /

column columns format a45 word_wrapped
select a.table_name, a.columns
from tmp_constraints a, tmp_indexes b

where a.table_name = b.table_name (+)
  and a.columns = b.columns (+)
  and b.table_name is NULL

/

drop view tmp_indexes
/
drop view tmp_constraints
/

---

>I have a problem which will grow bigger with my databases.
 

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

>My Oracle-oriented Netscape bookmarks and Lycos-searches did'n find one.
 

>Here is what I tried, but I'm not quite sure how correct the answer is.
>We have a CASE 5.1 dictionary, so any queries to that are also wellcome,
>but finding the information from existing database is even better.
 

>select table_name, constraint_name
>from user_constraints
>where (constraint_type = 'R' )
> and
> constraint_name not in
> ( select constraint_name
> from user_ind_columns i, user_cons_columns c
> where (i.table_name = c.table_name)
> and (i.column_name = c.column_name)
> and (i.column_position = c.position)
> )
>order by table_name, constraint_name
>/
 

>==========
>Lassi Salo, Nokia Telecommunications
> PO Box 33, FIN-02601 Espoo, Finland
>Email: Lassi.Salo_at_ntc.nokia.com
>FAX: +358-0-5112-2227
>Tel: +358-0-5112-2725
>==========

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Tue Dec 05 1995 - 00:00:00 CET