Re: Preferred method in creating primary key

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

hth
connor

-- 
=========================
Connor McDonald
http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue"