Re: Joining constraints to indexes in the system dictionary (Oracle8)

I did not realise that in Oracle7 the constraint name and index name did not have to be the same! I have checked on 7.3.4.4 and you are quite right.

Please could you correct the following select statement:-

   SELECT ... FROM user_constraints c, user_indexes i    WHERE c.constraint_type IN ('P','U')    and c.constraint_name = i.index_name;

So it does not rely on the constraint name and index name being the same.

Thanks, Ben

In article <88mvg6$771$1_at_nnrp1.deja.com>,   markp7832_at_my-deja.com wrote:
> In article <88msu4$5d7$1_at_nnrp1.deja.com>,
> Ben Ryan <benryan_at_my-deja.com> wrote:
> > Oracle 8.0.5
> >
> > Question:
> > In Oracle8 what is the approved way to join user_constraints
> > to user_indexes for constraints which are primary key or unique key
> > constraints?
> >
> > Background:
> > I have seen scripts for Oracle7 where it relies on the fact that the
> > name of the index matches the name of the constraint. e.g.
> >
> > SELECT ... FROM user_constraints c, user_indexes i
> > WHERE c.constraint_type IN ('P','U')
> > and c.constraint_name = i.index_name;
> >
> > However, I understand that in Oracle8 primary key constraints can be
> > enforced via pre-existing indexes. Meaning, I assume, the names will
> not
> > necessarily match.
> >
> > Thanks, Ben
> >
> Oracle would also use existing indexes in ver 7. I have been using
the
> same sql to generate primary key index rebuilds as I used in ver 7 so
> I do not think anything has really changed on this.
> --
> Mark D. Powell -- The only advice that counts is the advice that
> you follow so follow your own advice --
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Feb 19 2000 - 16:10:18 CST