RE: To foreign key or not to foreign key

From: Lex de Haan <lex.de.haan_at_naturaljoin.nl>
Date: Tue, 14 Dec 2004 18:20:30 +0100
Message-Id: <20041214172035.344F4A4E422@ha-smtp0.tiscali.nl>

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Fuad Arshad
Sent: Tuesday, December 14, 2004 17:34
To: oracle-l_at_freelists.org
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 Received on Tue Dec 14 2004 - 11:54:09 CST