Re: orace sql query

Steve Howard wrote:
On Jun 23, 6:18 pm, DA Morgan <damor...@xxxxxxxxx> wrote:
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
damor...@xxxxxxxxxxxxxxxx (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@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 ...
    (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
    ... Puget Sound Oracle Users Groupwww.psoug.org ... believe otherwise but were that not true you could not enforce ... a constraint MIGHT create an index or use an existing index to efficientenforce itself. ... a PRIMARY KEY constraint will either create an index or it will find an existing suitable index and use it. ...
    (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)
  • 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)