Re: How to disable constraint

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: 2 May 2003 10:06:35 -0700
Message-ID: <2687bb95.0305020906.1be31753@posting.google.com>

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 -- Received on Fri May 02 2003 - 12:06:35 CDT