Re: Deferrable FK

"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 Mon Aug 26 2002 - 04:28:19 CDT