How to delete a column from a table?

Yes, I know, I have seen it here sometime ago. But it was "then", and I thought I won't need it... but now I do... ;-)

So my long and tedious approach so far is:

1. Drop the foreign constraints I don't need any more due to the column being deleted (if I have these).
2. Since there is data in the table that I want to preserve, I create a temp table with all the data in it as:

   CREATE temp_table UNRECOVERBLE AS
   SELECT *
     FROM original_table;

3. Now I have to drop the original table, and this is where the 'pain' begins, because first I have to disable all constraints. Say I did, and disabled them. Then dropped the table - can I drop a table that has disabled constraints?

4. Now recreate the table (with less columns now), and reverse the select from the temporary table, using only the columns that I need, without the deleted one.

5. Enable all constraints.

Is there a better/faster way to do it? I have several tables that require this "operation", and might be more later.

Thanks,

-- 


[ Simon Goland       B-)>     sg_at_mda.ca ]

[   Without action there is no change   ]