Prev: QA Lead, Portland OR
Next: database management software ( DBSmart)
From: joel garry on 28 Jul 2010 13:18
On Jul 27, 10:16 pm, Mounilk <mounilkada...(a)hotmail.com> wrote:
> Hi,
> I am working on oracle database which has a few tables that do not
> have any primary keys. Unfortunately, I cannot modify the table
> designs.
>
> I need to find out the fields in the table that make the record
> unique.

Ironically, as you may notice from the other replies, this is an
ambiguous question with no unique answer.

What exactly is your need? I work on a database that has many tables
with no primary keys, so the app makes assumptions...

>
> I am not very familiar with Oracle and its system tables, but am
> pretty sure I should be able to get this information from some system
> table.
>
> Any help is greatly appreciated.
>
> Regards,
> MounilK

jg
--
@home.com is bogus.
http://www.cuddletech.com/blog/pivot/entry.php?id=1074
From: Mladen Gogala on 28 Jul 2010 22:32
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
From: joel garry on 29 Jul 2010 12:46
On Jul 28, 7:32 pm, Mladen Gogala <gogala.mla...(a)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

This may be an exception to the poor design suspicion, though I agree
with you that it normally would be. In this case, it is for
historical reasons - the app and programming language came out of the
early relational DEC world (Before RDB). In the early '80s, it was
common for software providers to write their own file routines. One
particular company wrote this relational software accessing its own
files, with a patent for getting any row in two disk accesses IIRC. I
first saw it at one of their customers in 1981. Later, they upgraded
the development tool to handle RMS files, then RDB files. Meanwhile,
they were bought by a series of companies, and eventually were
absorbed into a melange of app companies. In the early '90's, they
rewrote the tool to be able to handle multiple dbms engines, and
rewrote the apps. At this time, they were well aware of relational
design, as well as what customers needed, so in that sense it was well
designed. So think about how sophisticated primary keys were in the
Oracle 7.0 days, and its contemporaneous competitors. Anyways, the db-
blind tool had had an interesting, though arbitrary solution to
primary keys - whichever index was first alphabetically would be the
primary key. Of course, 20 years on this sounds stupid, but at the
time an app development tool could be far more relational than the
engines - and they did have RDB as their primary environment, using
the environment to catch other engines up to proper relational theory,
as well as allowing 3GL/4GL in the language - like all the stuff
people do in PL/SQL now (sometimes wrongly). In Oracle's case,
everything would be done by extracting rowid's and data. So you could
have automatic projections of how long reports will take to run,
report generators, and all that RAD stuff. It even worked with RMS
files up to a few years ago.

In the end, people like me wind up hearing about Agile and putting
everything in the app layer, and just roll our eyes.

But the apps themselves (now we're talking Enterprise level) have been
wrung out and improved, and are easily customizable for business
processes, and work really well for certain vertical markets (process
manufacturing in particular). Unfortunately, most Oracle customers
have either "stabilized" or moved to other players, most newer
customers just go MS and jam it all in. And the Chinese own it now -
a big part of their Oracle expansion in the '90s was because of NLS
and the eastern Pacific market.

jg
--
@home.com is bogus. "28. You walk into a grocery store, and see your
banks ATM machine being worked on. You see there is actually an
ordinary PC with an ordinary keyboard. On the screen is a SQL prompt,
and there is no one around. " - Me, 2003
http://www.informationweek.com/news/security/reviews/showArticle.jhtml?articleID=226300230&subSection=News
From: Mark D Powell on 29 Jul 2010 13:21
On Jul 28, 10:32 pm, Mladen Gogala <gogala.mla...(a)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 --

From: Mounilk on 2 Aug 2010 02:28
On Jul 30, 3:21 am, Mark D Powell <Mark.Powe...(a)hp.com> wrote:
> On Jul 28, 10:32 pm, Mladen Gogala <gogala.mla...(a)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 --

Hi All,
Thanks for your replies. I am a novice when it comes to Oracle db's,
pl-sql et al....I would appreciate if you can give me the query/
queries which i can run to find the information. I know it's kind of
spoonfeeding....but I'd appreaciate the help

Regards
First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: QA Lead, Portland OR
Next: database management software ( DBSmart)