Re: Preferred method in creating primary key

From: Howard J. Rogers <howardjr2000_at_yahoo.com.au>
Date: Thu, 14 Aug 2003 06:21:15 +1000
Message-Id: <pan.2003.08.13.20.21.14.150296@yahoo.com.au>

On Wed, 13 Aug 2003 23:05:18 +0800, Connor McDonald wrote:

> Jack Wang wrote:

>>
>> What is the preferred method in creating primary key?
>>
>> <Method 1>
>> CREATE TABLE T3
>> (
>> X NUMBER NOT NULL,
>> Y NUMBER
>> )
>> TABLESPACE USERS
>> ;
>>
>> CREATE INDEX T3_IDX ON T3
>> (X)
>> LOGGING
>> TABLESPACE INDX
>> ;
>>
>> ALTER TABLE T3 ADD (
>> CONSTRAINT T3_PK PRIMARY KEY (X));
>> </Method 1>
>>
>> <Method 2>
>> CREATE TABLE T3
>> (
>> X NUMBER NOT NULL,
>> Y NUMBER
>> )
>> TABLESPACE USERS
>> ;
>>
>> CREATE UNIQUE INDEX T3_IDX ON T3
>> (X)
>> LOGGING
>> TABLESPACE INDX
>> ;
>>
>> ALTER TABLE T3 ADD (
>> CONSTRAINT T3_PK PRIMARY KEY (X));
>> </Method 2>
>>
>> The subtle difference is that index created in method 2 is unique while
>> nonunique in method 1. If I disable pk in method 1, the nonunique index
>> remains intact. But if I disable pk in method 2, the unique index gets
>> hidden until I re-enable pk, and the index gets moved to default tablespace
>> (users) rather than INDX which is specified in the first place.
>>
>> Appreciated your advice.
>> Jack

> 
> In v9 you can have your cake and eat it too with the KEEP INDEX clause
> on constraints should you wish to disable/enable them
> 

Depends how hungry you are. Try disabling a primary key constraint thatīs using a unique index, and then specifying the keep clause. Works lovely. Now try entering a duplicate record... it will still get spat out. So how effective was your īdisableī request in the first place?? Er, not very.

Whatever the new syntactical possibilities, it still makes absolutely no logical sense to disable a unique constraint and leave behind an index that demands uniqueness. (The other way around, where you disable the constraint AND would like to drop the non-unique index that was enforcing it in one simple step is a much more sensible use of this new feature).

My own view is that Oracle should never have permitted īKEEPī when the index concerned was a unique one, since itīs a logical nonsense.

Regards
HJR Received on Wed Aug 13 2003 - 15:21:15 CDT