Toad World® Forums

Command to add indexes for all the child foreign key fields


#1

Is it possible to run a command that adds indexes for all the child foreign key fields.

It is possible to generate these indexes when selecting Creating Indexes for Foreign keys checkbox in the ddl generation script dialog, however the problem I have is that I cannot assign a table space to these indexes unless there is a way, so it would be nice if there was a way to add them to the model. As I am writing this I realized there is actually a way to add them by creating them and then using Update Model.

It would also be nice if the indexes can be created without selecting Create Tables. If I select Create Tables then Create Indexes is disabled. I believe in 2.25 it was possible to generate the indexes without creating the tables in the same script.


#2

Updating the model to reverse engineering just the indexes doesn’t work in 3.0.13.175. . I have no way to select only the indexes, plus the dialog is so slow!

I created those indexes in the database, I reversed engineer the model, then I tried to drag the indexes from one model to another to the same table but dragging indexes doesn’t works.

If TDM 3 were a physical object I would have thrown it on the window or smashed it on the walls.

While I think the support from Case Studio was great, TDM 3 is one of worst pieces of software I have ever seen that should have never been released in the state it is currently in. It is just a money grab that pisses me off.


#3

Hi Costa,

  1. Open the Relationship Properties dialog | General tab and select the “Create Index to Foreign Key” checkbox. -> The Index to Foreign Key tab will appear.
  2. Click the Index to Foreign Key tab to define a name of index to FK and to set up tablespace of the index.
  3. Confirm.
  4. Open the DDL Script Generation dialog | Detail Settings tab and select the “Create Indexes to Foreign Keys” checkbox.

It would also be nice if the indexes can be created without selecting Create Tables.
We will consider your request. CR # 46 975.

Thanks.

Regards,

Vladka + Mario


#4

Hi,

I want to have an index for each FK as well.
But I’m working with MSSQL 2005.
It seems that your tips how to create an index to each FK are Oracle specific.
I don’t see the described properties in the named dialogs.
Is there an already supported way how to create the requested indexes or do I have to create them by some TDM-scripts? When I have to create them via a script then I please for some tip how to do it.

With the best regards

Lada


#5

Hi Lada,

See below the script for MS SQL. The script will go through all relationships and add the index “IX_RelationshipName” to all child entities. The script ensures that index of the same name will not be created if the script is executed more times.

If you have any questions, please write us back. Thanks.

function main()
{
var i, j, Relation, ChildEntity, IndexName, Index, IndexItem, Attribute;
var app = System.GetInterface(“Application”);
var Model = app.Models.GetObject(0);
//The parameter in GetObject determines here with which model the script should work.
//0 is the first model listed in the Application View.
//1 is the second model listed in the Application View, etc.
Model.Lock();
for(i=0; i<Model.Relations.Count; i++)
{
Relation = Model.Relations.GetObject( i );

ChildEntity = Relation.ChildEntity;
IndexName = “IX_”+Relation.Name;
if (ChildEntity.Indexes.GetObjectByName(IndexName) == null)
{
Index = ChildEntity.CreateNewObject( 2012 );
//Create new Index (2012 is object type of PERIndex)
Index.Name = IndexName;
for (j=0; j<Relation.ForeignKeys.Count; j++)
{
Attribute = Relation.ForeignKeys.GetObject(j).AttrChild;
IndexItem = Index.CreateNewObject( 2013 );
//Create new Item of Index (2013 is object type of PERIndexItem)
IndexItem.Attribute = Attribute;
}

Log.Information("Index “+IndexName+” created on table "+ChildEntity.Name);
}
}
Model.UnLock();
}

Regards,

Vladka + Mario

P.S. Unfortunately, the community doesn’t allow me to attach files today. Don’t know why. Sorry.

Message was edited by: vladka