Re: how to alter table columns:

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 11 Aug 1999 15:26:07 GMT
Message-ID: <37ba9422.13496757@newshost.us.oracle.com>

A copy of this was sent to johng_at_mm.com (TurkBear) (if that email address didn't require changing) On Wed, 11 Aug 1999 14:04:06 GMT, you wrote:

>Zahid Khan <zahid_at_cc.gatech.edu> wrote:
>
>>
>>
>>TurkBear wrote:
>>
>>> Unless you are using 8i, you cannot....
>>>
>>
>>I am using 8i. Is there anyway to do it then?
>
> I was afraid you would say that....:-)
>
>I have heard that this is possible in 8i -- Perhaps Thomas Kyte is reading
>this and will reply with more info...( He knows everything.... :-) )
>

the original questions were:

1.] I have a table declared as :

create table cmpd (
cmpd_formula varchar2(20),
ca_number varchar2(10));

But now I want to change the name of the field ca_number to ca_no How do I do this?

2.] I have the cmpd_formula field declared as a primary key. But now I've decided that I don't want it to be the primary key. How do I make this change?

you cannot in any version of Oracle rename a column. In Oracle8i, release 8.1 you can drop a column so I suppose the steps of:

SQL> desc cmpd

 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 CMPD_FORMULA                                  VARCHAR2(20)
 CA_NUMBER                                     VARCHAR2(10)

SQL>
SQL> alter table cmpd add ca_no varchar2(10);

Table altered.

SQL> update cmpd set ca_no = ca_number;

(n) rows updated.

SQL> alter table cmpd drop column ca_number;

Table altered.

SQL>
SQL> desc cmpd

 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 CMPD_FORMULA                                  VARCHAR2(20)
 CA_NO                                         VARCHAR2(10)

would do it. I would suggest tho that a view is a more efficient way to do this (lots less io's on a big table).

SQL> desc cmpd

 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 CMPD_FORMULA                                  VARCHAR2(20)
 CA_NUMBER                                     VARCHAR2(10)

SQL>
SQL> rename cmpd to cmpd_table
2 /

Table renamed.

SQL> create or replace view cmpd ( cmpd_formula, ca_no ) 2 as
3 select cmpd_formula, ca_number from cmpd_table 4 /

View created.

SQL>
SQL> desc cmpd

 Name                                 Null?    Type
 ------------------------------------ -------- -------------------------
 CMPD_FORMULA                                  VARCHAR2(20)
 CA_NO                                         VARCHAR2(10)

2) for number 2 it is a simple matter of dropping the primary key constraint (any release of Oracle)

SQL> create table cmpd (
2 cmpd_formula varchar2(20) primary key, 3 ca_number varchar2(10));

Table created.

SQL> REM this gets the name of the primary key constraint on our table SQL> select constraint_name from user_constraints where table_name = 'CMPD' 2 and CONSTRAINT_TYPE = 'P'
3 /

CONSTRAINT_NAME

SYS_C007528 SQL> alter table cmpd drop constraint SYS_C007528;

Table altered.

>Sorry...
>
>
>
>
> -----------== Posted via Newsfeeds.Com, Uncensored Usenet News ==----------
> http://www.newsfeeds.com The Largest Usenet Servers in the World!
>------== Over 73,000 Newsgroups - Including Dedicated Binaries Servers ==-----

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Aug 11 1999 - 10:26:07 CDT