Steve Howard wrote:
> On Jun 23, 6:18 pm, DA Morgan <damor..._at_psoug.org> wrote:
>> 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
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org
>
>
> http://groups.google.com/group/comp.databases.oracle.server/browse_frm/thread/aa71c4ec34c19f5a/de0ca032eec03cba
Excellent. Howard Rogers is one of the people whose opinion on the
matter, I respect. I stand correct it would seem.
--
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 Sun Jun 24 2007 - 11:51:11 CDT
