Re: Drop a table with foreign key constraints
- From: "Octavian Rasnita" <orasnita@xxxxxxxxx>
- To: "Nigel Thomas" <nigel.cl.thomas@xxxxxxxxxxxxxx>, <m.haddon@xxxxxxxxx>
- Date: Fri, 30 Jul 2010 22:35:47 +0300
Hi Nigel,
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
