Re: How to disable constraint

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(CONSTRAINT_
> --------------------------------------------------------------------------------
> 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.

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