Comments embedded.
HJR
--
Oracle Resources : http://www.geocities.com/howardjr2000
========================================
"Thomas Kyte" <tkyte_at_us.oracle.com> wrote in message
news:9rn6u30c5q_at_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
>
Well, I did mention that the idea of loading order details before order
headers, but it all comes good by the time the commit is issued. This is
just a variation on that theme, really: *update* order header before
updating order details. Bad application design, though, isn't it? Suggests
(to me, at any rate) that the relational model has just been thrown out of
the window. Not something that would make me consider vast swathes of
deferrable foreign key constraints, anyway!
> >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..
>
OK, I over-egged the pudding... I should avoid the use of the word "never".
Of course if you load the data in a specific order (on courses, I refer to
it as loading the records 'backwards') you can pull the trick off... but I
would have thought that anything that made the order of loading rows
significant was a big no-no in real life, too. That was the real point.
You could equally do an initial load without the constraint being defined at
all, and then enabling it -so yes, there *are* ways of getting the data in,
but they are not "good" methods. The last one risks violating data. The
first one invests significance in the order of load where no significance
should be found.
> >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 ;)
>
I think we agree on a lot, actually!
HJR
> >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 - 16:50:11 CST
