Dear BBW Jim,

I have a lookup table with an expected cardinality of around 30 rows
I reference this table on a data table with an expected cardinality of 7
million rows.

Table Lookup:
-- Lookup_ID
-- Lookup_Name

Table Data
__ Data_ID primary key
__ Date
__ Lookup_ID references Lookup(Lookup_ID)

Question:
Is this safe to do?
Would this establishing of referential integrity constraint not cause an
index selectivity problem?

-----

Although this might look like a support question, what I'm really asking is,

Is this a problem area (architecturally - establishing reference
relationships) that needs a solution?

Or, is there nothing to worry about? specifically on selectivity issues that
the automatically generated index the reference creates.

At present, I have this rule with myself:

1. Only establish reference constraints when the relationship is a master /
detail one (I mean Header/Items).
2. Use Insert/Update/Delete triggers to establish Lookup constraints (to
cascade / restrict changes on Lookup tables to referencing tables)
-> on clients I use the transaction with READ COMMITTED

----

I realize that an automatically created Index is quite useful when the
nature of the reference is a Master-Detail one.
But, if the nature of relationship is a Look-up (Lookup ID validity
checking) the index on the referencing side is not necessary.

Is this suggestion okay for the new IB? ->

1. When the REFERENCE contains a CASCADE instruction -> automatically create
the index because this is most probably a Master/Detail relationship.

2. When the REREFERENCE does not contain a CASCADE instruction (restrictive)
then, do not automatically create an index because this is most probably
just a lookup relationship

Joseph Alba
jalba@...