Re: Problem with unique constraint creation

Hi EP,

My previous statement does !!

"deferrable" means that it *can*" be deferred but not necessarily is.

The default behaviour is to be deferrable *initially immediate*. This means the default behaviour of the constraint is in the traditional sense and Oracle will police the constraint at the time the statement is executed.

A deferrable *initially deferred* constraint means the default behaviour is to police the constraint at the time of a commit.

It's possible to change a session (eg. alter session set constraints = deferred;) to change this default behaviour. It's a unique constraint either way, it's just the way it's policed that can differ if it's deferrable.

In order to enable novalidate a constraint as you requested, the constraint has to be a deferrable constraint (either initially immediate or initially deferred) because Oracle needs to create a non-unique index in order to allow these duplicate rows. This can be changed later if required.

Hope this makes sense.

Richard

"EP" <ep_at_plusnet.pl> wrote in message news:agmfrs$jfv$1_at_news.tpi.pl...
>
> > When you create a constraint that you wish to enable novalidate, it must
> be
> > *deferrable* (but can be set deferrable initially immediate which is the
> > default).
> >
> > Change your statement to something like:
> >
> > alter table x add (constraint x_col_unique (col) deferrable enable
> > novalidate);
>
> OK but *deferrable* waits till the end of transaction, doesn't it ?
> Is it possible to create a unique constraint on the table with the
> duplicated rows ???
>
> Regards
> EP
>
>
Received on Fri Jul 12 2002 - 07:30:31 CDT