[AccessD] Indexes on a table from relationships

Ok, just to be clear, I could have Parent be the parent table to 40 tables
with RI, and I wouldn't go over the limit, at least from these alone.
However, if I tried to have 40 Foreign key fields with RI, I'd be out of
luck, unless I split Child into some OTO tables.

Mark Whittinghill
Symphony Information Services
612-333-1311
mwhittinghill at symphonyinfo.com
----- Original Message ----- 
From: "Jim Dettman" <jimdettman at earthlink.net>
To: "Access Developers discussion and problem solving"
<accessd at databaseadvisors.com>
Sent: Wednesday, August 13, 2003 10:12 AM
Subject: RE: [AccessD] Indexes on a table from relationships


> 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
>