Re: Deferrable FKs, any locking issues?

I can't think of a use for them being deferrable initially immediate. With primary and unique keys, such a clause helps ensure the indexes are created as non-unique, and thus protects the indexes from getting dropped when the constraint is disabled. But as there are no indexes associated inherently with foreign keys, that's of no relevance.

There is a good use for an actually deferred foreign key -a self referencing foreign key which, without being deferred, would never allow you to add any table data.

I can also see a need for an actually deferred foreign key on things like order headers/order details -it's possible the details get entered first, before the header is knocked into shape, but by the end of the entire process, the order comes good. Exactly what deferrable constraints are there for. But it would require a rather weird application for them to be needed, I think!

In general, I can't see much point in a *lot* of deferred foreign key constraints, and 'deferrable initially immediate' sounds to me more like 'I'm not sure what might one day be deferred, so I'll hedge my bets'. I'd say you need to know your application rather better than that!

As to drawbacks of deferrable initially immediate -there are none that I can think of. Certainly no additional locking issues that I'm aware of (but I might learn something from another poster). It just seems mostly pointless for foreign keys, that's all.

Regards
HJR

--

Oracle Resources : http://www.geocities.com/howardjr2000
========================================


<n.a.ekern_at_usit.uio.no> wrote in message news:9rm8it$95t$1_at_readme.uio.no...
We are planning on recreating our foreign keys from being 'not deferrable'
to be 'defearrable initially immediate'.

But, we have many foreign keys (more than 500 tables, more than 1000 fk's)
and ca 100 simultanously logged in users, so we are concerned whether this
can cause increased locking-activity in the database, and then
session-hangs for the users.

Anybody know if it is unwise to declare fk's as 'deferrable initially
immediate'?

Tia,
Njål A. Ekern