Re: how to alter table columns:

In article <934385698.3733.1.nnrp-04.9e984b29_at_news.demon.co.uk>,   "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
>
> 1: Best strategy - rename the table,
> create view cmpd as select renamed column list from renamed
table
>
> Alternative:
> create new_table unrecoverable as select renamed column list
from
> cmpd
> rename cmpd to backup_cmpd
> rename new_table to cmpd
>
> Oracle 8i option:
> alter table cmpd add column .....
> update cmpd set new_column = old_column
> alter table cmpd drop column ....
>
> 2: Alter table cmpd drop primary key;
>
> --
>
> Jonathan Lewis
> Yet another Oracle-related web site: http://www.jlcomp.demon.co.uk
>
> Zahid Khan wrote in message <37B05ED7.116B8E84_at_cc.gatech.edu>...
> >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?
> >
> >TIA
> >--zahid
> >
>
>

create new_table unrecoverable as select renamed column list from cmpd

     rename cmpd to backup_cmpd
     rename new_table to cmpd

I have a question regarding this strategy. What if the original table cmpd is being referenced in a number of other tables? As far as I know, the create as command does not propagate the references. And this necessarily means that we have to manually re-create all the references for the original cmpd table.

By the way, does Oracle have some ideological reservations against providing a command for renaming a column...

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Aug 12 1999 - 05:50:03 CDT