Re: orace sql query

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.

.

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
    ... "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
    ... 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: Create Table Issues
    ... unique index on BookID. ... > CONSTRAINT PubPK PRIMARY KEY ... > CONSTRAINT PubFK FOREIGN KEY REFERENCES Publisher ...
    (microsoft.public.access.gettingstarted)