Indexing on Junction Tables

[Kostas]

Hi all,

I was wondering what's happening in a typical junction table where the
primary key is the combination of two foreign keys from other tables.
I suppose Access automatically creates a concatenated index on the two
attributes that comprise the primary key.
Should I be indexing each attribute separately as well?

Cheers!

Konstantinos

[Allen Browne]

If you create a relation between two tables and enforce referential
integrity, Access creates a hidden index on the foreign key field. There is
therefore no need to index your foreign key field yourself.

That remains true for a multi-field key such as the OrderDetails in
Northwind, where there are two relations going to 2 different tables (Orders
and Products), because that involves an index on each one.

However if you have a multi-field relation (a relationship to another table
based on 2 or more fields), Access will give you a multi-field index.
Rushmore can then use the first field in the index as if it were indexed
itself, but you may want to index the 2nd and subsequent fields yourself.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kostas" <no*****@noemail.net> wrote in message
news:10*************@corp.supernews.com...

Hi all,

I was wondering what's happening in a typical junction table where the
primary key is the combination of two foreign keys from other tables.
I suppose Access automatically creates a concatenated index on the two
attributes that comprise the primary key.
Should I be indexing each attribute separately as well?

[Kostas]

Thanks,
It's funny I was just at your website a minute ago which I frequently
consult. Thank you by the way for the advice and the tips you share in your
site.
Is there a way to see and manipulate those hidden indexes that access
creates?

Konstantinos

"Allen Browne" <Al*********@SeeSig.Invalid> wrote in message
news:41***********************@per-qv1-newsreader-01.iinet.net.au...

If you create a relation between two tables and enforce referential
integrity, Access creates a hidden index on the foreign key field. There
is therefore no need to index your foreign key field yourself.

That remains true for a multi-field key such as the OrderDetails in
Northwind, where there are two relations going to 2 different tables
(Orders and Products), because that involves an index on each one.

However if you have a multi-field relation (a relationship to another
table based on 2 or more fields), Access will give you a multi-field
index. Rushmore can then use the first field in the index as if it were
indexed itself, but you may want to index the 2nd and subsequent fields
yourself.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kostas" <no*****@noemail.net> wrote in message
news:10*************@corp.supernews.com...

Hi all,

I was wondering what's happening in a typical junction table where the
primary key is the combination of two foreign keys from other tables.
I suppose Access automatically creates a concatenated index on the two
attributes that comprise the primary key.
Should I be indexing each attribute separately as well?

[Allen Browne]

Loop through the Relations collection. This kind of thing:

Function ShowRel()
Dim db As DAO.Database
Dim rel As DAO.Relation
Dim fld As DAO.Field

Set db = CurrentDb()
For Each rel In db.Relations
Debug.Print rel.Name, rel.Table, rel.ForeignTable
For Each fld In rel.Fields
Debug.Print , fld.Name, fld.ForeignName
Next
Next

Set fld = Nothing
Set rel = Nothing
Set db = Nothing
End Function

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Kostas" <no*****@noemail.net> wrote in message
news:10*************@corp.supernews.com...

Thanks,
It's funny I was just at your website a minute ago which I frequently
consult. Thank you by the way for the advice and the tips you share in
your site.
Is there a way to see and manipulate those hidden indexes that access
creates?

Konstantinos