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

If You for any reason must disable constraints PK and FK, for bulk loads, schema has circular references... ot whatever, method 1 is preferable with modified constraint: ALTER TABLE T3 ADD (
   CONSTRAINT T3_PK PRIMARY KEY (X))
   deferrable initially immediate ;

The Cost Based Optimizer will figure that a table with non-unique index and a PK constraint, if there is a row hit, it is unique.

Dependent on the version you are running supports this feature (8i +)

If there is close to zero chance of disabling constraints, I prefer method 2. It is more obvious and 'clean' for others to design from.

There is also a third method of inline primary key, that creates the unique index in the same tablespace as the table, and the constraint and index is named something like sys_cxxxxxx (not that nice).

CREATE TABLE T3
( X NUMBER NOT NULL
       primary key,
   Y NUMBER ). This version is often used by application installers, and I find that very unlucky.
You have a hard time getting the naming to be meaningful, constraint naming is impossible to change without dropping and recreating including index (on the wish list to Oracle: the alter table bbb modify constraint xxx rename to yyy).

A nonsupport way out of this is to update the sys.con$ table! No flaming - please. It is on Your own risk - but it works.

/Svend Jensen

Remove the spamkiller [S.] from reply address Received on Wed Aug 13 2003 - 06:26:28 CDT