Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: orace sql query
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 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.orgReceived on Sat Jun 23 2007 - 17:18:57 CDT