RE: To foreign key or not to foreign key

you are asking two things, right?
all foreign keys (and other constraints) should be declared in the database
-- there is (should be) no discussion about that. period.

whether or not to *index* your foreign keys, that's a different matter.
Indexes slow down DML, and the locking issues with unindexed foreign keys
only occur in certain scenarios, all well documented. having 15 indexed
foreign keys in a demanding OLTP environment does not sound fun to me.

Hope this helps, cheers,
 
Lex.
 
----------------------------------------------------------------
Tom Kyte Seminar: http://www.naturaljoin.nl/events/seminars.html
----------------------------------------------------------------
 

-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Fuad Arshad
Sent: Tuesday, December 14, 2004 17:34
To: oracle-l@xxxxxxxxxxxxx
Subject: To foreign key or not to foreign key

I know  this has been discussed here before and i did find a couple of
jonathan lewis's old posts 
 
The thing is we have a project where the consultants want to ensure  about
10-15 foreign  keys per tables to enforce parent child relationships.
I've seen  locking issues beforer and was wondering if the list could put
down a some pros and cons as to going or not going with a primary foriegn
key strategy.
This is a oltp type system and sub second response is what the enpd product
requires( isnt that the description of every project these days).
The consultants want every foreign key indexed. which i think is way too
much a performance degradation since inserts are going to be  major part of
the application.
 

--
http://www.freelists.org/webpage/oracle-l



--
http://www.freelists.org/webpage/oracle-l

Other related posts: