Toad World® Forums

How to include "create index" in DDL

Hello,

I found the “create index” won"t be in the DDL when the index name is the same as constraint name. I am using toad modeler to reverse engineer the DDL from the oracle database. Is there any way to force Toad modeler to have “create index” in the DDL generated?

Thanks for you help!

Hello,

you can change the order of objects before SQL/DDL script generation. Go to menu Model | Order of Generated Objects and switch to Extension tab. Select Index and set the value to "After CREATE TABLE statement. Order of Generated Objects dialog is also accessible from DDL Script Generation dialog | Detail Settings tab| Edit.

Regards,

David

Hello mail8mz,

I think you mean situation when in database(not in model) of Oracle is same index as key constraint. In this case Toad Data Modeler, doesn’t reverse index. So if Index not in model it will not be generate to DDL file. You can additional create index after RE. If index is present in model it is generate.

But if you have entity with key constraint without index, than index will be created automatically. This is reason why Toad Data Modeler doesn’t reverse theses indexes.

Daril

Daril,

You are right, I have problem to revese index. I do have index in the database oracle 12c.

I have both indexes and constraints in the database oracle12c. The index name and coresponding constraint name are the same. I reverse engineered the model from the database, and found no index in the entitie list in the model generted. I modified one of the index name in the database, the index with the new name appeared in the entity list of model generated. It seems Toad modeler 5.5 assume there is no index when the name of index and the constraint is the same.

My headach is that I have an existing system with many indentical index name and corresponding contraint name. The indexes DO exist. How can I reverse engineer to generate model with the indexes?

Thanks!

Hello mail8mz,

described behavior is correct.

When you create key constraint in oracle db, index with same name is created automatically. So you don’t need explicit specify index. Toad Data Modeler doesn’t reverse theses indexes to make model cleaner.

If you think, that is better to reverse theses indexes, please create task on Idea Pond.

Daril

Hi Daril,

The indexes of my system were created on the specific table space, is there anyway for me to reverse the table space for the index that has the same name as the key constraint?

Thanks!

Hi mail8mz,

if you have index in other tablespace information about this tablespace will be reversed to key constraint properties dialog on tab “Using Index Properties”. See screenshot bellow

KeyIndexProperties.png

Daril

I am using Toad Data Modeler 5.5.3.20. I am not able to find the tab “using index properties”. Would you please provide more details? Thanks!

Double Click on Entity to display Entity Properties Dialog. Go to Tab Keys and double click on any key to display key properties dialog.

Key Properties Dialog can display also from Model Explorer. You need right click to some Key and from popup menu select "Edit".

Regards Daril

The index is on specific table space in the database already, I am not able to get the table space information from reverse engineer. Is there any configuration to allow me to reverse the table space information of the index?

Go to Options frame in RE Wizard and try to check option "Index Attributes".

Thanks, it works for me. I missed that option just now.