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.

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

On 28.07.2010 07:16, Mounilk wrote:
You can achieve uniqueness in oracle by utilization of unique indexes or
unique constraints. To find them you can query
select * from user_indexes where uniqueness='UNIQUE'
resp.
select * from user_constraints where constraint_type = 'U'
After you have the names, you can drill down to columns - there are
user_ind_columns and user_cons_columns.
For all user_ views there are as well all_/dba_ which may be less/more
sutitable for your needs.


Best regards

Maxim

On Tue, 27 Jul 2010 22:16:44 -0700, Mounilk wrote:

Why would a RDBMS need to maintain a record of uniqueness? How would it
do that? Nope, there are tools which allow you to determine whether there
are duplicates and add unique constraints. RDBMS per se does not maintain
anything about the uniqueness of columns nor is it required to.



--
http://mgogala.byethost5.com

On 28.07.2010 14:18, Mladen Gogala wrote:
Adding to that: even if you find out which columns *currently* have
unique values per record (see below) this does not guarantee that it is
always the case. OP, the only real chance is to either investigate
uniqueness constraints or unique indexes as Maxim has indicated or find
out otherwise which columns are supposed to be unique (documentation,
application code, ask someone). But frankly, if there are columns in
the schema which are supposed to be unique but don't have appropriate
constraints then I'd question the schema design.

Note, if you want to find out the current state of affairs you can do
something like

select count(*) / count(distinct cola) as rows_per_val_cola
, count(*) / count(distinct colb) as rows_per_val_colb
....

Columns which turn up 1 are unique right now. But watch out for NULLS!
And be prepared that this is likely slow if your tables are big.

Kind regards

robert

--
remember.guy do |as, often| as.you_can - without end
http://blog.rubybestpractices.com/

Mounilk <mounilkadakia (AT) hotmail (DOT) com> writes:

If you just need to identify a row to issue an update command, using the
rowid pseudocolumn may help. This is kind of implicid primary key. But
keep
in mind that rowids should *never* be stored in the database since they
are invalidated e.g. by export/import.

Hope that helps,
Lothar

--
Lothar Armbrüster | lothar.armbruester (AT) t-online (DOT) de
Hauptstr. 26 |
65346 Eltville |

On Jul 27, 10:16*pm, Mounilk <mounilkada... (AT) hotmail (DOT) com> wrote:
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...

jg
--
@home.com is bogus.
http://www.cuddletech.com/blog/pivot/entry.php?id=1074

On Wed, 28 Jul 2010 10:18:03 -0700, joel garry wrote:

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

On Jul 28, 7:32*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
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/...Section=N ews

On Jul 28, 10:32*pm, Mladen Gogala <gogala.mla... (AT) gmail (DOT) com> wrote:
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 --

On Jul 30, 3:21*am, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:
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