[AccessD] Indexes on a table from relationships

Jim Dettman jimdettman at earthlink.net
Wed Aug 13 10:12:06 CDT 2003


Mark,

<<1. This is how I understand indexes in Jet mdbs.  You can have up to 32
indexes on a table.  If you have a relationship with Referential integrity,
Access creates an index on the linked field.  If you set an index on the
same field, you have double indexed it.  In this case, you would want to
remove the index from the indexes popup, and just let the autocreated index
be the index for that field.>>

 That is correct.

<<2. Let's say we have two tables, Parent and Child.  Parent links OTM on
Parent.ParentID (PK) join Child.ParentID.  Will this count as one of the 32
allowed indexes for Parent, Child, or both?>>

  Child as long as RI is enforced.  If RI is not enforced, no additional
indexes are created.  If it is, Access/JET creates a hidden index on the
child side.


Jim Dettman
President,
Online Computer Services of WNY, Inc.
(315) 699-3443
jimdettman at earthlink.net

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mark
Whittinghill
Sent: Wednesday, August 13, 2003 11:00 AM
To: accessd at databaseadvisors.com
Subject: [AccessD] Indexes on a table from relationships


2 questions:

1. This is how I understand indexes in Jet mdbs.  You can have up to 32
indexes on a table.  If you have a relationship with Referential integrity,
Access creates an index on the linked field.  If you set an index on the
same field, you have double indexed it.  In this case, you would want to
remove the index from the indexes popup, and just let the autocreated index
be the index for that field.

Is this correct, or what is wrong with this?

2. Let's say we have two tables, Parent and Child.  Parent links OTM on
Parent.ParentID (PK) join Child.ParentID.  Will this count as one of the 32
allowed indexes for Parent, Child, or both?

Thanks,



Mark Whittinghill
Symphony Information Services
612-333-1311
mwhittinghill at symphonyinfo.com


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com




More information about the AccessD mailing list