Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Deferrable FKs, any locking issues?

Re: Deferrable FKs, any locking issues?

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 30 Oct 2001 13:43:31 -0800
Message-ID: <9rn6u30c5q@drn.newsguy.com>


In article <3bdefca8$0$23885$afc38c87_at_news.optusnet.com.au>, "Howard says...
>
>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.
>

how about for the guy that wants to do an "update cascade" in a stored procedure?

set constraints all deferred;
update parent primary key
update child fkey
set constraints all immediate

>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.
>

EMP has had the classic example for this (mgr to empno) and you don't need deferrable constraints to add an emp, you just insert an emp with a NULL mgr (KING). The next can point to him and so on..

>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.
>

I think they are only good for doing an update cascade in this case (which I am personally against having to be in the situation of doing -- so I guess in a way, I agree with you ;)

>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
>
>
>

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 
Received on Tue Oct 30 2001 - 15:43:31 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US