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
Received on Sat Jun 23 2007 - 19:57:30 CDT
