In reply to a question in another group I read:

"If the lookup table has very few rows compared to the main tables that use
it, then don't apply a formal FOREIGN KEY constraint to the main tables
referencing it unless you want really rotten performance. (This is known
as the Low Selectivity Problem. <g>)". (Helen Borrie)

The statement might have been specific to a particular situation, but it makes be wonder
about efficiency vs declarative integrity.

Suppose there are N records of animals and 20 breed types where the breed types are stored
in a lookup table and connected to the animal table via an integer field.

Normally I would place a foreign key constraint on animal into breed type to ensure my
programming doesn't store an invalid number in the animal table.

There are 3 types of uses for this connection.

1. Display animal details including their breed type.

2. Select animal records based on selected breed type or types.

3. Filter on breed type the animal records that have already been selected into a grid.

For which of these 3 types of use and for what values of N is performance enhanced by not
declaring a foreign key?

Typically N will be between 500 and 100,000 animals.

Thanks.

Raymond Kennington