Re: Oracle system table
Date: Wed, 28 Jul 2010 14:54:48 +0200
Message-ID: <8bankvF4irU1_at_mid.individual.net>
On 28.07.2010 14:18, Mladen Gogala wrote:
> On Tue, 27 Jul 2010 22:16:44 -0700, Mounilk wrote:
>
>> 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.
>
> 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.
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/Received on Wed Jul 28 2010 - 07:54:48 CDT