Preferred method in creating primary key

From: Jack Wang <nospam_at_nospam.com>
Date: Wed, 13 Aug 2003 06:47:40 GMT
Message-ID: <gal_a.3241$zE1.1984@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 Received on Wed Aug 13 2003 - 01:47:40 CDT