Re: Constraints and indexes

Daniel Morgan wrote:

> Luc Gyselinck wrote:
>
>>We are running Oracle 8.1.7.4.0
>>
>>I am in a never ending discussion about how primary keys and unqiue keys
>>should be created. So I need a third opinion.
>>
>>One camp says one should create constraints first with the USING INDEX
>>clause, so the indexes that support the constraints, are created first.
>>The other indexes are created afterwards.
>>
>>The other camp says that one can create the indexes first and the
>>constraints afterwards. The constraints will (using Tom Kyte's words)
>>'kidnap' an index. Of course, you can end up with constraints that do not
>>use the 'correct' index. Does it matter? Constraints are constraints,
>>indexes are indexes!
>>
>>What if the first camp does an export of the database, followed by an
>>import into another? Import creates the indexes first and then the
>>constraints, wright?
>>
>>--
>>Luc Gyselinck
>>
>>
> I fail to see any point to the question. A primary key constraint
> consists of a table constraint and an
> associated unique index.

Incorrect. The index does not have to be unique, and indeed will not be under all sorts of beneficial conditions.

>A unique constraint consists of a column
> constraint and an associated unique
> index.

Also Incorrect. The index does not have to be unique, and indeed will not be under all sorts of beneficial conditions.

>Why would anyone care which came first ... the chicken or the egg?
>

Largely agreed. However, when you use the USING INDEX clause, you do not (until 9i anyway) have complete access to the entire create index syntax, and therefore something might need to be specified which you can't. Creating the index first does allow you complete control, so it might be preferable.

However, the entire issue is moot in 9i, since the USING INDEX clause now takes a complete create index statement.  

> If the index doesn't already exist use the USING INDEX clause to define
> the index's storage parameters
> and tablespace. If it does it does ... kidnap it and move on.
>

Regards
HJR

-- 
--------------------------------------------
See my brand new website, soon to be full of 
new articles: www.dizwell.com.
Nothing much there yet, but give it time!!
--------------------------------------------