Re: not null and indexes

"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