Re: How to disable constraint

From: Daniel Morgan <damorgan_at_exxesolutions.com>
Date: Sat, 03 May 2003 07:38:40 -0700
Message-ID: <3EB3D470.39FDCAC6@exxesolutions.com>

Richard Foote wrote:

> "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

You are correct. But I come from a background that says you NEVER put bad data into a production environment. In a situation such as you describe the raw records would go into a staging schema to be cleaned up there. Then and only then would I move it into production tables. And the point in defering constraints would be to avoid load sequence issues rather than with the thought that somehow I was going to make it all better and reapply constraints while those person's that normally use the production application go on holiday.

--
Daniel Morgan
http://www.outreach.washington.edu/extinfo/certprog/oad/oad_crs.asp
damorgan_at_x.washington.edu
(replace 'x' with a 'u' to reply)

Received on Sat May 03 2003 - 09:38:40 CDT