Re: Drop a table with foreign key constraints

Thanks for your clarifications and also thanks to Dick and Stefano.

> As Dick explained earlier, this won't work because the constraints are
> dependent on the table:
>
> - FK constraints from this table to others will be dropped when the table
> is dropped
> - FK constraints from child tables to this one will prevent the table
> being dropped unless you use CASCADE CONSTRAINTS (or explicitly drop them
> first)

Too bad. It would have been nice to have a feature like create or replace table ...
which would preserve the constraints of the existing table.

> The alternative solutionsfor the OP are:
>
> - as Dick says, don't bother reorganising columns

I am storing the balance sheet positions which are displayed in a certain order, and it would be easier if the table would have them in that order. And I might need to add some sub-positions in that balance sheet...

> - as Stefano says, use DBMS_REDEFINITION

It sounds pretty good from what I've seen, so I will try it.

> - or capture the definition of the table and all referencing constraints,
> indexes etc using DBMS_METADATA, then do the reorganisation, then restore
> the constraints and indexes
>

Unfortunately this doesn't sound like an easy solution. :-)

Octavian

--
http://www.freelists.org/webpage/oracle-l