Re: Constraints and indexes

Howard J. Rogers wrote:

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

Thanks for the correction. That is one of those things I know and keep forgetting. The refresher is
appreciated.

-- 
Daniel Morgan
http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp
http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)