Re: How Change Column Name?

I may be wrong, but.....

I believe that the primary key in pii_tbl is 'named' uniquely because its name, as interpreted by the dictionary, includes the table name ( hence its name originally is pii_tbl.pii_pk and its components are the qualified column names that is table_name.column_name ) so another table should be able to use the same name -

But, as I said above,this may not be the case since my particular database uses few primary keys ( Yeah, I know, but our data currently is not 'normalizable'; we're working on it ) so my experience in this area is limited.

John Greco
Oracle DBA ( 7.3,Netware 4.11)

stantow_at_ibm.net (Stan Towianski) wrote:

>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 Mon Dec 08 1997 - 00:00:00 CET