Re: orace sql query

From: DA Morgan <damorgan_at_psoug.org>
Date: Sat, 23 Jun 2007 11:10:00 -0700
Message-ID: <1182622196.823706@bubbleator.drizzle.com>

fitzjarrell_at_cox.net wrote:

> On Jun 22, 3:45 pm, DA Morgan <damor..._at_psoug.org> wrote:

>> EdStevens wrote:
>>> On Jun 22, 12:15 pm, sathishkes..._at_gmail.com wrote:
>>>> i want to create table with primary key but with out index
>>> How do you think Oracle enforces the PK?
>> Not with an index. Indexes have nothing to do with primary key
>> enforcement. Consider, for example, a deferred constraint which
>> is built with a non-unique index.
>> --
>> Daniel A. Morgan
>> University of Washington
>> damor..._at_x.washington.edu (replace x with u to respond)
>> Puget Sound Oracle Users Groupwww.psoug.org

>>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:3051352977204

"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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org

Received on Sat Jun 23 2007 - 13:10:00 CDT