Re: Disabling Fkey Constraint & there of ..

In article <rppw9076pr.fsf_at_queen.plaza.nt.com>, JP Srinath <jp_at_queen.plaza.nt.com> wrote:
>I think you are missing the point here. Let me try to explain what I am
> trying to do.
>
> I want to make some some structural changes to the parent table - So I
> intend to export the data, disable Fkey of the children, drop the parent
> , recreate the parent, import data & enable the Fkey of the children.

This is the pattern I go by. Except that you must actually DROP the FK's, not just disable them. There's an Oracle quirk where it will continue to give you the misleading error :

  ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Even if all FK's referring to your table have been disabled. You must instead DROP them.

>
> Why should the DBMS stop me from dropping the parent ? I know very well
> that the children are hanging in the air - but at the same time I can
> make sure that all the Fkey's are present in the parent before I enable
> the Fkey constraint !
>

This is probably a throwback to Oracle 6, where it ONLY supported the FK restriction in the sense the Table definition, and not ROW data.

I use a TCL script (not oratcl, but another variant) to spit out two scripts, one which drops the constraints, and another which rebuilds them.

> Thanks ,
>
> JP

Hope it helps,
Kevin Fries

-- 
-------------------------------------------------------------
Kevin Fries                         kelfink_at_ecst.csuchico.edu
CPD/PB, C Developer/DBA  http://www.ecst.csuchico.edu/~kelfink