RE: To foreign key or not to foreign key

FK's are good.
Index every FK except those where the parent is never updated/deleted AND every millisecond counts.

Best suggestion is to use tests to show how much slower it will be to have all 10-15 of them. If the parent tables are designed well, and indexed properly, a simple insert is not likely to suffer much from lots of FKs.

If it turns out that 10-15 FKs slows the INSERT down too much, consider data integrity and its possibility of ruin. Are the users allowed to fill the FK'd columns free-form, or are they filled by listboxes on the GUI side, listboxes populated from the parent reference tables?

If the users cannot insert junk into the columns, and you won't be updating or deleting the parent table, you might be able to get away without the FK.

• bill c.

-----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 10: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 - 12:37:49 CST