Re: How to disable constraint

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Sat, 3 May 2003 23:19:44 +1000
Message-ID: <S7Psa.27806$1s1.411504@newsfeeds.bigpond.com>

"Daniel Morgan" <damorgan_at_exxesolutions.com> wrote in message news:3EB31C70.81D56BA7_at_exxesolutions.com...
> Mark D Powell wrote:
>
> > Daniel Morgan <damorgan_at_exxesolutions.com> wrote in message
news:<3EB27A7B.26181506_at_exxesolutions.com>...
> > > cyril wrote:
> > >
> > > > Hello,
> > > > How to disable all foreign constraints in a table with a single SQL
order ?
> > > >
> > > > sample : 'alter table mytable disable constraint all'
> > > >
> > > > thanks
> > >
> > > Can not be done. They can be deferred in a single statement but not
disabled.
> > >
> > > Which is good because it keep people from doing it so readily.
> > >
> > > The fact that you can do something doesn't mean you should.
> >
> > Cyril, what Daniel did not mention is that it is easy to generate the
> > commands to do this for those times such as when you want perform a
> > mass load task and want to perform the constraint validation at the
> > end rather than row by row.
> >
> > UT1> set pagesize 90
> > UT1> l
> > 1 select 'alter table '||lower(table_name)||chr(10)||
> > 2 ' disable constraint '||lower(constraint_name)||';'
> > 3 from dba_constraints
> > 4 where table_name = 'DR_PART_DEFECT'
> > 5* and constraint_type = 'R'
> > UT1> /
> >
> >

'ALTERTABLE'||LOWER(TABLE_NAME)||CHR(10)||'DISABLECONSTRAINT'||LOWER(CONSTRA INT_
>
> --------------------------------------------------------------------------

---

> > alter table dr_part_defect
> > disable constraint dr_part_defect_fk1;
> >
> > alter table dr_part_defect
> > disable constraint dr_part_defect_fk2;
> >
> > ....
> >
> > However, you should not be doing things like this as a part of normal
> > processing.
> >
> > HTH -- Mark D Powell --
>
> I didn't mention it intentionally. :-)
>
> And in the case of loads ... I still prefer defer over disable.
> --

Hi Daniel,

But they're not really the same thing ?

If you have suz data you want to clean up after a load, you still need to disable the constraints and re-enable with novalidate (obviously with a deferrable constraint). You're only delaying (until commit time)the possible inevitable by "only" having an initially deferred deferrable constraint unless it's been disabled as well.

It all depends on "why" the OP wishes to disable these constraints.

I prefer a "white" over a "red" but with a t-bone, I'll go for a Coonawarra red every time ;)

Cheers

Richard Received on Sat May 03 2003 - 08:19:44 CDT