Re: Deferrable FK

Thanks for the reply and very interesting.

I suspect (and I'm guessing) the reason deferrable constraints wasn't chosen initially was because deferrable constraints didn't exist.

In the case of FK constraints it does beg the question - what type of FK constraint or usage would make it not deferrable? It would seem simpler to make all FK constraints deferrable and leave it up to the programmer to decide which method they prefer.

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:6gma9.15328$g9.48049_at_newsfeeds.bigpond.com...
>
> "Mike Burden" <bmike_at_eidosnet.co.uk> wrote in message
> news:3d69ea7a$0$27145$afc38c87_at_news.eidosnet.co.uk...
> > We have an application that seems just right for deferred FK
constraints.
> > It's
> > a utility which moves data from one database to another using business
> > rules.
> > This app is not too bright so it has trouble when moving data from
tables
> > with FK constraints because it hits the
> > tables in no particular order. However because the source and target
> > database use the same design and all tables take part I know that all FK
> > constraints will be ok
> > once all the data from a given key range has been moved i.e. at the
commit
> > point.
> >
> > Now deferrable constraints solves the problem nicely.
> > Small amounts of testing has confirmed this to be the case so my next
step
> > would be to convert all FK constraints to deferrable.
> >
> > But this raises the questions.
> >
> > Is 'not deferrable' and 'deferrable initially immediate' the same in
> > performance terms?
>
> Yes. There is absolutely no difference in terms of performance as there is
> no difference in the way they behave *by default*.
>
> > If so why do we have 'not deferrable'?
>
> Not Deferrable means the constraint can not be altered to a deferrable
state
> in a session. So you specify Not Deferrable when it never ever makes sense
> for it to be deferrable or for the constraint to be enabled with
novalidate.
>
> However, a deferrable constraint can be flipped from actually being
> deferrable (initially deferred) or not deferrable (initially immediate).
> This "flipping" can be done via the alter session set
> constraints=deferred/immediate/default. Also a deferrable constraint can
be
> enabled with novalidate whereas a not deferrable constraint can not.
>
> So a deferrable constraint is only 'deferrable' (it can be changed) with
the
> initially immediate/deferred denoting it's "default" behaviour.
>
> > How does Oracle actually do deferred checking as this, at first sight,
> seems
> > quite complex?
>
> Not entirely sure on the ins and outs of this (I've actually heard
> conflicting versions/opinions).
>
> > Schema Manager seems to indicate (by the fact it won't let me do it)
that
> > FKs can't
> > just be change to deferrable so it seems they must be dropped and
> > recreated which makes the whole exersise more complicated.What's all
that
> > about?
>
> A couple of issues here. If a constraint could be swapped from deferrable
to
> not deferrable then it should have been made deferrable, that's the idea.
> The behaviour can be changed. If it's a Not Deferrable constraint then it
> can not be swapped and hence you need to drop and recreate as necessary.
>
> Another issues is one of indexes. If a constraint is Not Deferrable, then
> Oracle will create a Unique index to police the constraint. If a
constraint
> IS deferrable, then Oracle creates a non unique index to police the
> constraint (as it's possible in a deferred constraint to have duplicate
> values in a point of time). To alter the constraint to be deferrable after
> it has been defined as NOT deferrable means the unique index would have to
> be dropped and a non unique index created in it's place. I guess if you
are
> going to be doing this in the background, we might as well make it a
little
> harder for this change to take place.
>
> Hope it make some sense.
>
> Cheers
>
> Richard
> >
> >
> >
> >
> >
> >
>
>
Received on Tue Aug 27 2002 - 16:57:31 CDT