compound index

Question

Hi

I created a compound index by selecting three fields in the table. I named it 'CompIndex'.  I did the same in the other table that I want to connect. Both tables have the same three fields that I included in creating the CompIndex. In the data base tools when I click the relationship the tables are there. But the compound index (?Composite Key) that I created ('CompIndex') does not show up in either table to let me drag and create a relationship. What am I dong wrong?

Of course I can drag the three individual fields to the other table and create a relationship. But that just delays the execution. Am I right?

Thank you

HNIM2010

Answer 1

To create a relationship on composite keys firstly drag from one column of the key in the referenced table to the corresponding column in the referencing table.  Then in the Edit Relationship dialogue add the other columns which form the keys.   Enforce the relationship and click on OK to close the dialogue.

Answer 2

You don't specify whether these are unique indexes or the relationships are one to many or what. 

Personally, I do not like using composite keys. They are cumbersome to use in joins and they will affect performance. Definitely use a multi-field unique index to impose uniqueness on the combination of fields, But then you can use an Autonumber as your PK and use that value as your Foreign key in the child table. This eliminates the need for redundant data, will make your joins perform better and give you the same functionality for imposing uniqueness.

Answer 3

As always! You have been of great help.

Thank you

HNIM2010