Re: Deferrable vs. Not-Deferrable Constraints

"JustAnotherDBA" <jadba_at_bellsouth.net> wrote in message news:W700a.1$0I6.0_at_news.bellsouth.net...
> It seems to me that all constraints should be created as deferrable these
> days.
>
> Of course, I would think we should also set them initially immediate too,
in
> order to preserve the usual expected behavior, until at least more people
> (especially 3rd party apps) are aware constraints can be enforced (and
cause
> errors) at commit time too .
>
> I suppose 1 exception might be if you really want a unique index (for
> performance reasons?),

There are ZERO performance differences between a deferred and an immediate constraint. The optimizer is smart enough to realise that a non-unique index enforcing a unique/primary key constraint is just as unique as a unique index doing the same thing: ultimately, both are unique in their contents, if not in their definition.

So go for it: the only problem with deferred constraints is that one violation causes an entire transaction to be rolled back (with non-deferred ones, only the violation is rolled back). Otherwise, they are an entirely good thing, and they have no performance issues.

Regards
HJR
> then you might not want to use the deferrable option
> on the unique or primary key type constraints (since these type
constraints
> don't generate unique indexes when using the deferrable option).
>
> I am looking for any comments for why I would NOT want to ALWAYS use the
> deferrable option (to have the flexibility of deferring checking until
> commit time).
>
>
>
>
Received on Wed Feb 05 2003 - 05:49:51 CST