Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: not null and indexes

Re: not null and indexes

From: Mark C. Stock <mcstockX_at_Xenquery>
Date: Sun, 13 Feb 2005 16:50:38 -0500
Message-ID: <ldednWMuwaIpVpLfRVn-qw@comcast.com>

"Niall Litchfield" <niall.litchfield_at_dial.pipex.com> wrote in message news:420f9b25$0$16585$cc9e4d1f_at_news-text.dial.pipex.com...
> "Malcolm Dew-Jones" <yf110_at_vtn1.victoria.tc.ca> wrote in message
> news:420f964e_at_news.victoria.tc.ca...
>> vertigo (ax178_at_wp.pl) wrote:
>> : Hello
>>
>> : Does oracle create automaticly indexes on not null columns ?
>>
>> No.
>>
>> But if you define constraints on a column then oracle may implement that
>> constraint using an index on the column.
>>
>> Prime example being a primary key - it must be unique and a unique index
>> is used to enforce that - so in that case those columns will be in an
>> index. Also in that case, the columns involved will end up being not
>> null
>> columns.
>
> Isn't this the *only* such example. As such I'd view it as an exception.
> Incidentally if the column is already indexed (even with a non-unique key)
> then the index won't be created (and more to the point) dropped with the
> constraint.
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.niall.litchfield.dial.pipex.com
>

Good addtional regarding the pre-existing index.

Clarification for those not familiar with this -- the behavior described above is true for both PRIMARY KEY and UNIQUE constraints, but no other constraints (i.e., not for CHECK, FOREIGN KEY REFERENCES or NOT NULL)

++ mcs Received on Sun Feb 13 2005 - 15:50:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US