Re: No future for DB2 - slightly off-topic, discusses what people are being taught at uni

"Mark A" <nobody_at_nowhere.com> wrote in message news:d9idnXLXjqxxhXDfRVn-qw_at_comcast.com...

> 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.

While I agree with you, I would add that for code lookups like Title (Mr, Mrs, Ms)
I would rather go with a check constraint than a FKY. Anything with a low cardinality
and known static values should be handled in a check constraint. At least I would. Received on Sun Jul 31 2005 - 13:38:36 CDT