Re: Deferrable vs. Not-Deferrable Constraints

Thanks for all the comments.

Just to clarify, when we do change our constraints, I will want to keep the current default behavior where the INSERT generates an error if the constraint is violated.

So, we will recreate the constraints with the deferrable option, but also with the initially immediate option too.

Anyway, I do like the option of turning off constraint checking until I commit . This could be useful during activity like doing data changes (mass load or fixing a problem some app caused or for whatever reason) of parent/child table data.

Seems like this setup should be a default behavior especially when there is no difference with the current default behavior UNTIL you do the set or alert session command .

Or, am I missing something ?

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:XK60a.41231$jM5.104555_at_newsfeeds.bigpond.com...
>
> "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 - 23:06:39 CST