View attachment 114128
And that is pretty much all I need to convince me that there is an index there. Access created it when the Foreign Key constraint was created, and its name reflects that aspect if its origin. Call it virtual, call it hidden, or call it an unexplained phenomenon. It's there.
If SSMA doesn't recognize it and migrate it, that could be an oversight or a design decision.
By default, Access will create an Index on any begins or ends with any of the names in the auto-index box as seen here.
As to why they were not created automatically in the OP's database, I am not sure, unless the tag words were changed or removed. It should have automatically created indexes for the PersonID Column when it was added.
It also explains why Indexes were created on the Test DB when I imported the Table1 and Table2. i.e., "AutoIndex on Import/Create". I am therefore left with the impression that the OP removed the Index. I have never had this fail on me.
Anyway, this is just all new to me. I just never had the issue appear for me to ever question it.
When I ask CoPilot the question, this is what I get (see #2):
Chat, get answers, create amazing content, and discover information effortlessly with Bing's AI-powered chat. Transform the way you search and get answers with Microsoft Copilot in Bing.
sl.bing.net
In Access VBA, when working with ADO (ActiveX Data Objects), you might encounter situations where it appears that a virtual index is created on a foreign key (FK) column even though there is no visible index. Let’s explore this further:
- Foreign Keys and Indexes:
- A foreign key is a constraint that establishes a relationship between two tables. It ensures that values in a column (the child table) correspond to values in another column (the parent table).
- Contrary to popular belief, a foreign key itself does not automatically create an index. It is a separate concept from indexes.
- However, it makes sense to index all columns involved in foreign key relationships. Indexing these columns can improve query performance when enforcing referential integrity.
- Why FK Columns May Appear Indexed:
- When you create a foreign key relationship in Access, the underlying database engine (such as Jet or ACE) may implicitly create an index on the FK column.
- This index is not always visible in the Access user interface, but it exists to optimize query execution related to referential integrity checks.
- The index allows the database engine to efficiently validate that no other rows reference the row being modified or deleted.
- Seek Method and Index Property:
- Indexed Property in Access:
In summary, while foreign keys themselves do not automatically create indexes, it’s good practice to index the columns involved in FK relationships. This can enhance query performance and ensure referential integrity. If you encounter unexpected behavior related to virtual indexes, consider checking the underlying database engine’s behavior and any hidden indexes that may exist.