Toad World® Forums

Reverse engineering Access : Indexes created without items

Hi there!

I’m currently evaluating the Toad Modeler for usage in our organization. I’m using the trial version 3.6.6.7 against an existing MS Access 2000/2003 database to reverse engineer modifiy and recreate it.

We also have MySQL, MS-SQL and Oracle databases running so I’ll be looking at transfering our models from one engine to the others.

I encountered a problem regarding indexes for some of the concerned tables: indexes have been created for a few of them, but with no fields indicated in the “item” section of the “Indexes” tab.

There we no such indexes on the original Access tables, and it went seemlessly when I recreated the Access DB, but created a syntax error when I converted the model to test it against a MySQL DB on the following statement:

CREATE INDEX EQUIPEMENTEQUIPEMENT_IMPLANTE ON EQUIPEMENT_IMPLANTE ();

That error (creating an index without indicating the indexed attribute) went unnoticed by the “Verify model” function. Is this a normal behavior? Have I missed a manipulation in my process (other than manually checking all indexes?)

Thanks for the insight!

Hi Pascal,

would you mind sending the model (or at least a part of it) to modeling@quest.com so we can analyze the problem?

Thank you.

Regards,
Lukas

Hi Lukas and thanks for the reply!

I’ll be sending the model, though as I think I can explain it, now… :blush:

It would seems that the tables are imported with indexes on every key composing the primary key, which is standard behavior, IMO.

But while learning to use TDM, I’ve had issues creating new relations without automatically creating new attributes in child tables (the parent table’s primary key gets copied the the child table, even if an attribute of the same type/name already exists.)

So I’d recreate the relation, reassign the child key to the existing child attribute, and that must be where the error (or mine?) occured; if there was an index on the key; and that one have been deleted, then recreated; the index based on it dont get refreshed. And indexes aren’t created for each relation, even identifying one.

The model attached is what TDM gave me after the RE of one of our DB, here. You’ll see that most table aren’t in relations to each others, but that’s the work I have to do; the source DB didn’t have them.

The relations that gave issues are the ones joining the table “equipement_implante” with its parents (“patient”, “equipement”, “procedure”, etc…) Yes, someone named a table PROCEDURE… sigh…

In the end, all is well; thanks for the reply
RE_EEP.txp (1.01 MB)

Hi Pascal,

it seems you deleted attributes from your indexes by accident when deleting the old attributes. Instead, you should have used FK Mapping (have a look in Help file for it tab Contents | Model | Physical Data Model | Entity Relationship Diagram | Keys | Foreign Keys | FK Mapping), which would do the job properly.

And also read about relationships in Contents | Model | Physical Data Model | Entity Relationship Diagram | Relationships | Relationship Types.

Regards,
Lukas

Hi Lukas,
thanks for the pointers; that’s effectively what I did wrong.

cheers

My pleasure, Pascal.
In case you have any other questions, don’t hesitate and post them here!

Regards,
Lukas