Re: Alter Table help needed.

From: Andrew Allen <ajallen_at_mailhost.det.ameritech.net>
Date: Thu, 20 Sep 2001 23:28:48 GMT
Message-ID: <3BAA7BC8.9AE037C9@mailhost.det.ameritech.net>

Rob Panosh wrote:
>
> Hi,
>
> My sample alter statement:
> --------------------------
> ALTER TABLE myTable
> ADD CONSTRAINT PK_MYTABLE PRIMARY KEY (myTestColumn)
> /
>
> Could somebody please revise the above demonstrating how to store this
> primary key in a different table space than the table? I need an example on
> how to set phyical options.
>

Prior to 8.1:
CREATE UNIQUE
 INDEX pk_mytable

    ON mytable (mytestcolumn)

       TABLESPACE tablespace_i
          STORAGE(. . .        <---<<<- optional
         PARALLEL (DEGREE 4)   <---<<<- optional

;
ALTER TABLE mytable

        ADD CONSTRAINT pk_mytable
            PRIMARY KEY (mytstcolumn)
            USING INDEX

;

8.1 and later:
CREATE
 INDEX pk_mytable

    ON mytable (mytestcolumn)

       TABLESPACE tablespace_i
          STORAGE(. . .        <---<<<- not required if you you LMT
W/Uniform Storage
         PARALLEL (DEGREE 4)   <---<<<- optional

;
ALTER TABLE mytable

        ADD CONSTRAINT pk_mytable
            PRIMARY KEY (mytstcolumn)
            USING INDEX

;

Note that 8.1andlater doesnot require unique index to enforce PK. In fact, it prefers not to use unique index. This is necessary to implement deferred PK constraint.

HTH

-- 
Andrew Allen
Livonia, MI
E- Mailto:ajalle_at_ameritech.net

Received on Thu Sep 20 2001 - 18:28:48 CDT