Re: orace sql query

Charles Hooper wrote:
On Jun 23, 2:10 pm, DA Morgan <damor...@xxxxxxxxx> wrote:
fitzjarr...@xxxxxxx 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...@xxxxxxxxxxxxxxxx (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@xxxxxxxxxxxxxxxx (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
.

Relevant Pages

  • Re: orace sql query
    ... "Oracle enforces all PRIMARY KEY constraints using indexes. ... believe otherwise but were that not true you could not enforce ... For example, a PRIMARY KEY constraint ... Puget Sound Oracle Users Groupwww.psoug.org ...
    (comp.databases.oracle.server)
  • Re: orace sql query
    ... You can not have a primary key or unique constraint without an index. ... Oracle won't allow it. ... Given the combinations of rely, enable, and validate, it's quite surprising ...
    (comp.databases.oracle.server)
  • Re: orace sql query
    ... Puget Sound Oracle Users Groupwww.psoug.org ... "If you try to trick Oracle by realizing that a primary key can be ... Oracle will use the index T_IDX to enforce the primary key. ... "In order to enforce uniqueness, and that includes a UNIQUE constraint ...
    (comp.databases.oracle.server)
  • Re: orace sql query
    ... believe otherwise but were that not true you could not enforce ... For example, a PRIMARY KEY constraint ... Puget Sound Oracle Users Groupwww.psoug.org ... Create a generic table without a primary key: ...
    (comp.databases.oracle.server)
  • Re: orace sql query
    ... believe otherwise but were that not true you could not enforce ... Puget Sound Oracle Users Groupwww.psoug.org ... ALTER TABLE T1 MODIFY (PRIMARY KEY (C1)); ... SQL> select constraint_name, constraint_type, deferrable ...
    (comp.databases.oracle.server)