By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,827 Members | 2,211 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,827 IT Pros & Developers. It's quick & easy.

Hidden Index from Foreign Keys

P: n/a
I ve been told from Allen and others that when enforcing referential
integrity Access creates a hidden index for the foreign key, therefore, I do
not need to re-index it myself.

However, I noticed that if the foreign key has the same name as the name of
the field to which it corresponds in the other table, then it appears as
indexed in the indexes list.

For example:
Table PERSON. Field: PersonID
TABLE EMPLOYEE Field that is lForeign key: EmployeeID
Employee appears as non-indexed so I assume from what I ve been told that
Access already created a hidden index on it after ref. integrity was
enforced between the two tables.

But if:
TABLE PERSON. FIELD: PersonID
TABLE EMPLOYEE: FIELD that is Foreign key: PersonID,
then the field appears as indexed in the EMPLOYEE table.
Should I delete this index as there should be a hidden index as well when RI
was enforced?

Cheers!
Konstantinos
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
Kostas wrote:
I ve been told from Allen and others that when enforcing referential
integrity Access creates a hidden index for the foreign key, therefore, I do
not need to re-index it myself.

However, I noticed that if the foreign key has the same name as the name of
the field to which it corresponds in the other table, then it appears as
indexed in the indexes list.

For example:
Table PERSON. Field: PersonID
TABLE EMPLOYEE Field that is lForeign key: EmployeeID
Employee appears as non-indexed so I assume from what I ve been told that
Access already created a hidden index on it after ref. integrity was
enforced between the two tables.

But if:
TABLE PERSON. FIELD: PersonID
TABLE EMPLOYEE: FIELD that is Foreign key: PersonID,
then the field appears as indexed in the EMPLOYEE table.
Should I delete this index as there should be a hidden index as well when RI
was enforced?


What is probably happening here is that Access is set to automatically
add an index to fields that end in "ID", it's in the options and if you
know what you're doing when designing a database, best turned off.

--
Pretentious? Moi?
Nov 13 '05 #2

P: n/a
Thank you!
I found it out, and turned it off.
That explains why my CountryCode field was indexed too!

Regards,
Konstantinos

"Trevor Best" <nospam@localhost> wrote in message
news:41**********************@auth.uk.news.easynet .net...
Kostas wrote:
I ve been told from Allen and others that when enforcing referential
integrity Access creates a hidden index for the foreign key, therefore, I
do not need to re-index it myself.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.