Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to delete a column from a table?

Re: How to delete a column from a table?

From: Lassi Salo <Lassi.Salo_at_ntc.nokia.com>
Date: 1997/03/12
Message-ID: <3326A095.50B6@ntc.nokia.com>#1/1

Nevin Hahn wrote:
> Most importantly is to identify the constraints and to know what they are
> doing. You have to actually drop the constraints, disabling them doesn't
> work. So you have to recreate the constraints after the table is
> recreated. Once you're clear on the constraints then do a:
>
> drop table original_table cascade constraints
>
> I would create the table again with proper sizing and then insert the
> records into the new table with:
>
> insert into new_table
> select * from temp_table
>
> Now recreate the constraints with:
>
> alter table new_table
> add constraint ...

That is "the right way of doing it".

I've done it few times. Watch out for time&resources when working with big tables.

I've tested (but not used) another quick-and-somewhat-dirty solution:

  1. Modify column to NULL allowed.
  2. Update column to NULL.
  3. Add check constraint COLUMN XXX IS NULL ( name the constraint like TABALIAS_XXX_NULLED, so you can track them easily when rebuilding database in future, you then get rid of the columns)

After this you won't want to show your schema to a real C.J. Date fan...

Received on Wed Mar 12 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US