Re: Drop a table with foreign key constraints
- From: Nigel Thomas <nigel.cl.thomas@xxxxxxxxxxxxxx>
- To: m.haddon@xxxxxxxxx
- Date: Fri, 30 Jul 2010 17:20:21 +0100
Mike
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)
So you cannot preserve (in the data dictionary) the definition of any
constraint dependent on a table while that table is itself being dropped and
recreated.
The alternative solutionsfor the OP are:
- as Dick says, don't bother reorganising columns
- as Stefano says, use DBMS_REDEFINITION
- 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
Regards Nigel
2010/7/30 Mike Haddon <m.haddon@xxxxxxxxx>
> set constraints all deferred;
>
> <drop and recreate table the way you need to. (make sure data is back)>
>
> set constraints all immediate;
>
> Mike
>
>
>
>
Other related posts:
