Re: Changing Column Name

In article <BuBryy.Ep8_at_well.sf.ca.us>, mharper_at_well.sf.ca.us (Michael J. Harper) writes:
> This should be an easy one :-)
>
> How do I change the name of a column in a table?
>
> Michael J. Harper
> mharper_at_well.sf.ca.us

Not so easy. You could try,

create table my_temp as
select a,b,c d from a_table; -- table my_temp will have columns a,b,d drop table a_table;
rename my_temp to a_table;

However, you would end up losing all synonyms, views, indexes, grants, and constraints in the process. You could get around that by exporting the table with those objects, using the above statements, then import everything except the table. But, this does not "rename" references to the column in views, synonyms, indexes and some grants. I seem to remember the SQL1 standard saying that since columns rely on names, not position, that this operation was disallowed as an integrity violation, however, I have absolutely no proof that this is true, (and I know some databases allow this).

Most designers I know would do something like this :

alter table a_table add column d ...; -- same as format for "c" update a_table set d = c;
update a_table set c = NULL;

Then, wait until the next generation of your application to get rid of column C.

Scott Stephens (from my own experience, NOT official word of my company) Received on Sat Sep 12 1992 - 08:57:24 CEST