Re: How to disable ALL constraints ?

This is from the Oracle8 documentation of the enable clause for constraints

ENABLE
 specifies that the constraint will be applied to all new data in the table. Before you can enable a referential integrity constraint, its referenced constraint must be enabled.
ENABLE VALIDATE additionally specifies that all old data also complies with the constraint. An enabled validated constraint guarantees that all data is and will continue to be valid.
 If you place a primary key constraint in ENABLE VALIDATE mode, the validation process will verify that the primary key columns contain no nulls. To avoid this overhead, mark each column in the primary key NOT NULL before enabling the table's primary key constraint. (For optimal results, do this before inserting data into the column.)  ENABLE NOVALIDATE ensures that all new DML operations on the constrained data comply with the constraint, but does not ensure that existing data in the table complies with the constraint.

 Enabling a primary key or unique key constraint automatically creates a unique index to enforce the constraint. This index is dropped if the constraint is subsequently disabled, causing Oracle to rebuild the index every time the constraint is enabled. To avoid this behavior, create new primary key and unique key constraints initially disabled. Then create nonunique indexes or use existing nonunique indexes to enforce the constraints.

So to achieve the behaviour you want look at enable novalidate. However as Jerry's post implies if you have a constraint it should be there for a good reason. This good reason is likely to apply to your existing data as well. "Joachim Pense" <joachim_pense_at_hotmail.com> wrote in message news:86guj7$t31$1_at_nnrp1.deja.com...
> In article <388BDF7A.BC8AAA01_at_erols.com>,
> Jerry Gitomer <jgitomer_at_erols.com> wrote:
>
> > Also after you enable constraints after the import check for valid
> > constraints again since Oracle will not enable a constraint that is
> > invalid. So if you find one you have to fix it.
> >
> > --
>
> What does this mean in detail?
> Does it mean, when I reenable a constraint, the whole table will be
> checked by Oracle for validity of the new constraint (and you suggest
> to check them before to avoid an exception)? This seems not to be what
> most users (me included) want. What we want is to have only the new
> records checked and leave the old ones untouched.
>
> Joachim
>
> --
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Mon Jan 24 2000 - 09:56:05 CST