Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: orace sql query

Re: orace sql query

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 23 Jun 2007 15:18:57 -0700
Message-ID: <1182637134.471808@bubbleator.drizzle.com>


Charles Hooper wrote:
> On Jun 23, 2:10 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> fitzjarr..._at_cox.net wrote:
>>> >From the documentation:
>>> "Oracle enforces all PRIMARY KEY constraints using indexes.
>> To the best of my knowledge the document is incorrect.
>>
>> The index is used to speed the search for a duplicate value ... not
>> to enforce it.
>>
>> I will gladly stand corrected on this if someone has reason to
>> believe otherwise but were that not true you could not enforce
>> uniqueness with a non-unique index.
>>
>> I didn't find the support I wanted in the time I have available
>> right now but let me quote from Tom Kyte:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:3...
>> =========================================================================
>> "The relationship between indexes and constraints is:
>>
>> o a constraint MIGHT create an index or use an existing index to
>> efficient[ly] enforce itself.  For example, a PRIMARY KEY constraint
>> will either create an index (unique or non-unique depending) or it will
>> find an existing suitable index and use it.
>>
>> o an index has nothing to do with a constraint.  An index is an index.
>>
>> So, a constraint MIGHT create/use and index.  An INDEX is  an INDEX,
>> nothing more, nothing less."
>> =========================================================================
>>
>> Please send me the link to the document in question. Thanks.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>
> I am now a bit confused on this topic, or are we reading things
> differently?
>
> Create a generic table without a primary key:
> CREATE TABLE T1 (
> C1 VARCHAR2(20),
> C2 VARCHAR2(20));
>
> Table created.
>
> Create a unique index on the C1 column of the table:
> CREATE UNIQUE INDEX IND_T1 ON T1(C1);
>
> Index created.
>
> Specify that column C1 is the primary key for T1:
> ALTER TABLE T1 MODIFY (PRIMARY KEY (C1));
>
> Table altered.
>
> Take a look at the indexes for the table:
> SELECT
> INDEX_NAME
> FROM
> DBA_INDEXES
> WHERE
> TABLE_NAME='T1';
>
> INDEX_NAME
> ----------
> IND_T1
>
> DROP INDEX IND_T1;
>
> ERROR at line 1:
> ORA-02429: cannot drop index used for enforcement of unique/primary
> key
>
> The above seems to be in agreement with the posts by David
> Fitzjarrell, Ed Stevens, Frank van Bortel, and the Oracle
> documentation. Or, is this not a valid test?
>
> Charles Hooper
> IT Manager/Oracle DBA
> K&M Machine-Fabricating, Inc.

I know what the error message says.

And I know what the document Frank posted the link to says.

And until corrected by someone I stand by what I wrote.

Consider this:

SQL> select constraint_name, constraint_type, deferrable

   2 from user_constraints
   3 where table_name = 'UCLASS';

CONSTRAINT_NAME                C DEFERRABLE
------------------------------ - --------------
PK_UCLASS                      P DEFERRABLE
CC_UCLASS_CLASS_NAME           C NOT DEFERRABLE

SQL> SELECT index_name, index_type, uniqueness

   2 FROM user_indexes
   3 WHERE table_name = 'UCLASS';

INDEX_NAME                     INDEX_TYPE                  UNIQUENES
------------------------------ --------------------------- ---------
PK_UCLASS                      NORMAL                      NONUNIQUE

SQL> DROP INDEX pk_uclass;
DROP INDEX pk_uclass

            *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

I don't believe a non-unique index is being used for enforcement of a unique condition. Any comment Tom? Jonathan? Richard? Howard? Except to speed the up the search.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Sat Jun 23 2007 - 17:18:57 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US