Re: Preferred method in creating primary key

"Jack Wang" <nospam_at_nospam.com> wrote in message news:<gal_a.3241$zE1.1984_at_edtnps84>...
> 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

Jack, I think the answer to which method is best for defining a PK depends on your application requirements, deferred constraints, and your preferences.

We do not use deferred constraints so we define the PK index and the PK constaint in an alter table statement where we specifiy the tablespace and storage (considering we use locally managed tablespaces with uniform extents we could drop the storage clause but we like to see values). This gives us unique indexes, but based on the version 8.0 manual where Oracle said uniqueness is an application concept the method of first creating the non-unique index and then adding the constaint would seem a logical choice especially if you use or intend to use deferred constaints which I believe require non-unique indexes.

IMHO -- Mark D Powell -- Received on Wed Aug 13 2003 - 09:51:11 CDT