Re: How Change Column Name?

From: Michael Serbanescu <mserban_at_postoffice.worldnet.att.net>
Date: 1997/12/06
Message-ID: <66b07i$p95_at_bgtnsc01.worldnet.att.net>#1/1


Anything wrong with:

ALTER TABLE pii_tbl_old DROP PRIMARY KEY CASCADE ?,

apart from the fact that you will have to re-create the FOREIGN KEY constraint(s), if any, that REFERENCE the primary key of table pii_tbl_old ? You could re-create them from the export file or from the USER_/ALL_/DBA_CONSTRAINTS and USER_/ALL_/DBA_CONS_COLUMNS tables (prior to dropping the PRIMARY KEY constraint). E-mail me if you need help in writing the query to identify the FOREIGN KEY constraints.

Hope this helps.

Michael Serbanescu



On 6 Dec 97 00:37:14 GMT, 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 Sat Dec 06 1997 - 00:00:00 CET

Original text of this message