Re: Oracle system table

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Thu, 29 Jul 2010 10:21:58 -0700 (PDT)
Message-ID: <40afefbe-4cdf-47e5-9561-f7a9572548a1_at_f42g2000yqn.googlegroups.com>



On Jul 28, 10:32 pm, Mladen Gogala <gogala.mla..._at_gmail.com> wrote:
> On Wed, 28 Jul 2010 10:18:03 -0700, joel garry wrote:
> > What exactly is your need?  I work on  a database that has many tables
> > with no primary keys, so the app makes assumptions...
>
> Having tables with no primary keys is usually a sign of poor design, and
> therefore the application itself is extremely suspicious. When table is
> created, there must be some kind of criteria for identifying the records
> and selecting them from the table. That is what the primary keys are for.
> There has been, as you're probably well aware, a long debate about
> "natural" vs. "generated" (or "unnatural") primary key. Each approach has
> its advantages but there must be a primary key for every table.
>
> --http://mgogala.byethost5.com

As a historical note it used to be very common for vendor products to not declare PK constraints in the database but to include unique indexes on the tables. This was in part because some database products did not support constraints and in part due to the menatality of "if I have a unique index why do I need to declare a PK constraint also". As mgogala said the application really should be using PK, UK, and FK constraints.

As mentioned dba_indexes can be used to find indexes built with the unique attribute and dba_ind_columns can be used to find the list of columns that make up the index.

HTH -- Mark D Powell -- Received on Thu Jul 29 2010 - 12:21:58 CDT

Original text of this message