| DA Morgan 2005-07-31, 1:23 pm |
| Mark A wrote:
> "rkusenet" <rkusenet@hotmail.com> wrote in message
> news:3l3q1gF10mdvsU1
@individual.net...
>
>
> The subject of foreign keys and indexes has been discussed in the Oracle
> newsgroup recently.
>
> Not all foreign keys need (or should have indexes). Some foreign keys are
> merely connected to code tables that are used to make sure a valid value is
> used, and they are never joined. The example used in the Oracle thread is
> division_code on sales transaction table that relates to a division_code
> table with only 3 rows (divisions).
>
> Having an index on the foreign key for division_code would slow down inserts
> on the sales transaction table, and would never be used for queries
> (cardinality of 3 is too low for a RDBMS to use this index for queries),
> except for the extremely unlikely event of someone trying to change or
> delete a row in the division_code table.
>
> So creating an index on a foreign key should not be automatic.
A syntax such as the following would solve the problem:
ALTER TABLE tab1
ADD CONSTRAINT fk_tab1_col1
FOREIGN KEY (col1)
REFERENCES tab2(col2)
USING INDEX;
Giving the power to decide whether to index, or not, to the
database professional. It needn't be an all, or nothing, syntax.
--
Daniel A. Morgan
http://www.psoug.org
damorgan@x.washington.edu
(replace x with u to respond)
|