Jim Dettman
jimdettman at earthlink.net
Wed Aug 13 11:08:20 CDT 2003
Mark, <<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.>> As long as it was the one of the possible 32 indexes you could have on the parent side, then yes you could do that. <<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.>> Correct. Generally I've only seen the limit a problem in some HR apps and scientific applications. Most folks don't hit it if the DB is designed properly (not saying you didn't either<g>). 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 12:02 PM To: Access Developers discussion and problem solving Subject: Re: [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 > _______________________________________________ AccessD mailing list AccessD at databaseadvisors.com http://databaseadvisors.com/mailman/listinfo/accessd Website: http://www.databaseadvisors.com