RE: To foreign key or not to foreign key
- From: "Bill Coulam" <bill.coulam@xxxxxxxx>
- To: <fuadar@xxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 14 Dec 2004 12:30:20 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Fuad Arshad
Sent: Tuesday, December 14, 2004 10:34 AM
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.
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
