Re: how can I determine the primary key for a table?

From: joel garry <joel-garry_at_home.com>
Date: Wed, 15 Apr 2009 14:13:26 -0700 (PDT)
Message-ID: <b7f0fa65-e607-4455-8851-1a2035b38791_at_v1g2000prd.googlegroups.com>



On Apr 15, 2:25 am, Frank van Bortel <frank.van.bor..._at_gmail.com> wrote:
> ddf wrote:
> > On Mar 30, 12:48 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:
> [snip]
> >>> On Mar 30, 11:20 am, "Larry W. Virden" <lvir..._at_gmail.com> wrote:
> >>>> I have a set of oracle tables which were set up by people no longer
> >>>> available for consultation.
> >>> A good reason why the data dictionary contains the information it
> >>> does.
> >>>> When I select from the all_indexes table, asking about the index_name
> >>>> for one of my tables, it lists:
> >>>> SQL> select index_name, uniqueness
> >>>>         from all_indexes
> >>>>         where table_name = 'TABLE_H'
> >>>>         ;  2    3    4
> >>>> INDEX_NAME                     UNIQUENES
> >>>> ------------------------------       ----------------
> >>>> TABLE_H_PK                      UNIQUE
> {snip!!]
>

> > I do understand this, however the example the OP posted clearly shows
> > a unique index enforcing the primary key so I tailored the example to
> > his given conditions.
>

> > I'd prefer to not confuse the OP with facts he's not using.
>

> > David Fitzjarrell
>

> Sorry to disagree - but the OP only has a unique index, with
> a confusing name, which led you to believe there is a primary key
> constraint. All the OP did was querying ALL_INDEXES.
>

> An index, unique or not, is not the same as a constraint.
>

> There's another view to be queried for constraints, as Maxim showed.
> --
>

> Regards,
> Frank van Bortel

Yes, I was very surprised to discover an enterprise software package had more than a thousand tables, none of which had a primary key. I had no reason to look until I tried to implement materialized views and got some obscure errors.

The app extends the data dictionary with its own, uses some Oracle features, and does its own version of others, some of which are very useful, and of course one is primary key integrity. This can be a problem when using other tools to access the database, and one must gingerly approach using sql - especially using sql for DDL must be avoided, otherwise the apps view of the dictionary becomes disjointed. Sometimes the rules for determining the "primary key" can be confusing, and the primary key can change as a result of certain DDL - the app language allows implicit domains to control joining tables in code, an odd syntax to do outer joins and a number of syntaxes to do natural joins are just a couple of the consequences.

Enquiring of the vendor, I got a response that I could go ahead and add them if I wanted, Oracle's rules for primary key usage of indices are well established. The person investigating seemed quite enthusiastic and pleased someone would actually care about this. I had been confused a couple of times as to why there were no 'P' type constraints, but just chalked it up to something obvious I was missing, never really thinking it through until it caused an actual problem - namely, not being able to use an Oracle feature. One of these days I'll try adding it to the tables in question, it would help a repetitive I/O spike due to doofus coding I hastily put together after wasting so much time on this, to push data elsewhere... I just have a problem adding to >1000 tables, some of which may "change" primary keys in the future...

jg

--
_at_home.com is bogus.
My alma mater mentioned in /. : http://www.allosphere.ucsb.edu/
Received on Wed Apr 15 2009 - 16:13:26 CDT

Original text of this message