RE: To foreign key or not to foreign key

Fuad,

I put foreign keys everyplace they are required. No exceptions.

Indexes to support foreign keys can be pushed a little bit. I will *not* put indexes on foreign keys if the parent table is rarely updated. Examples are reference or lookup tables where the list of codes is pretty much static. If the parent table is never updated, then you will not have locking problems.

But the remaining indexes to support foreign keys are a must. They will save your bacon.

Good Luck!

Tom

-----Original Message-----

From: Fuad Arshad [mailto:fuadar_at_yahoo.com] Sent: Tuesday, December 14, 2004 11:34 AM 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:56:14 CST