Re: How Change Column Name?

From: Stan Towianski <stantow_at_ibm.net>
Date: 1997/12/06
Message-ID: <34889e3a.0@news1.ibm.net>#1/1

Hi,

Good try guys, but...

I can create the 2nd table now and get the proper records in, and even get the 'grants' set by copying the stmt.s out of an export, but...
What do you do about a primary key???
This is what I used:
I can either create a new table like pii_tbl2 and copy the rows from the
orig. table to it and then rename pii_tbl2 to pii_tbl, OR I can rename pii_tbl first to pii_tbl_old and then do this stmt:

create table MFG.pii_tbl
(po_no ,

item_no ,

inspect_date   ,
passed_qty     ,
rejected_qty   ,
initials       ,
seq_no        ,
    CONSTRAINT pii_pk PRIMARY KEY (po_no, item_no, inspect_date)
        USING INDEX
            TABLESPACE INDEXES
            STORAGE ( INITIAL 200K   NEXT 50K
                      MINEXTENTS 1  MAXEXTENTS 100
                      PCTINCREASE 0 )

)
TABLESPACE MFG
STORAGE ( INITIAL 300K NEXT 100k

          MINEXTENTS 1  MAXEXTENTS 100
          PCTINCREASE 0 )

as select * from pii_tbl2;

Now, the problem is that either way the primary key gets in the way. If I do it the first way then I have to create the new table with a different PRIMARY KEY NAME like pii_pk2, but then I do not know how to rename it afterwards back to what it should be.

Or if I rename first, then I did 'alter table disable primary key'. This drops the primary key, but not totally! It probably physically dropped the index but the name pii_pk is still defined so I cannot use the above sql stmt to create my new table.

What do I do about this?
I do not know of a way to drop constraint pii_pk...

Thanks.

(posted newsgroup reply and sent email)

stantow_at_ibm.net
stanjenn_at_eaglequest.com Received on Sat Dec 06 1997 - 00:00:00 CST