Re: modify constraint?

Hi Owen,

This is the key reason.

A non-deferrable constraint is generally policed by a unique index (a unique index is created unless a suitable index already exists).

A non-deferrable constraint *must* be policed by a non-unique index (as it's possible for a point of time during a transaction for duplicate values to exist).

By converting a non-deferrable constraint to a deferrable constraint, you are potentially forcing Oracle to drop a unique index and create in it's stead a non-unique one. This is obviously a non-trivial exercise that could take a considerable period to complete. It's also one that you may not want to simply be "performed under the covers" and manage independently.

Therefore, Oracle forces you to implicitly perform this operation by dropping the non-deferrable constraint (and it's associated index) and re-creating the deferrable constraint (and it's associated index, which you may incidentally wish to create manually beforehand and combine with another appropriate concatenated value).

There is a reason for the madness :)

Richard
"Owen Gibbins" <oweng_at_autoplan.ws> wrote in message news:errK9.226070$C8.628060_at_nnrp1.uunet.ca...
> What's the point of MODIFY CONSTRAINT if it doesn't work (e.g. to make a
> constraint deferrable).
> Does anyone know why it is necessary to drop and recreate a constraint in
> order to change it?
>
>
Received on Mon Dec 16 2002 - 06:36:53 CST